﻿using BonjourQuotation.DataAccess.Entity_Framework;
using BonjourQuotation.ObjectModel.AccountData;
using BonjourQuotation.ObjectModel.BusinessData;
using BonjourQuotation.ObjectModel.ErrorData;
using BonjourQuotation.ObjectModel.LoggerData;
using BonjourQuotation.ObjectModel.SetupData;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BonjourQuotation.DataAccess.Repositories
{
    public class SetupRepository : ISetupRepository
    {
        private readonly BonDataEntities _context = new BonDataEntities();
        private readonly log4net.ILog logger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        #region ManageQuotationRepository
        public SetupManageQuotationsGridBData LoadManageQuotations(int PageNumber)
        {
            SetupManageQuotationsGridBData Quotations = new SetupManageQuotationsGridBData();
            Quotations.Rows = new List<SetupManageQuotationsGridRowBData>();
            Quotations.Rows = (from Quota in _context.tblQuotations
                                orderby Quota.QuotaID
                                select new SetupManageQuotationsGridRowBData()
                                {
                                    QuotaID = Quota.QuotaID,
                                    QuotaCode = Quota.QuotaCode,
                                    QuotaName = Quota.QuotaName,
                                    Confirmed = Quota.Accepted,
                                    Active = Quota.Active,
                                }).Take(PageNumber * SetupManageQuotationsGridBData.PageCount).ToList();
            Quotations.Confirmed = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            Quotations.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return Quotations;
        }
        public SetupManageQuotationsGridBData LoadSearchedManageQuotations(SetupManageQuotationsGridBData Data)
        {
            var FilterQuota = _context.tblQuotations.AsQueryable();
            if (Data.QuotaID != null)
                FilterQuota = FilterQuota.Where(Quota => Quota.QuotaID == Data.QuotaID);
            if (Data.QuotaCode != null && Data.QuotaCode.Length > 0)
                FilterQuota = FilterQuota.Where(Quota => Quota.QuotaCode.Contains(Data.QuotaCode.Trim()));
            if (Data.QuotaName != null && Data.QuotaName.Length > 0)
                FilterQuota = FilterQuota.Where(Quota => Quota.QuotaName.Contains(Data.QuotaName.Trim()));
            if (Data.SelectedConfirmed != null)
                FilterQuota = FilterQuota.Where(Quota => Quota.Accepted == Data.SelectedConfirmed);
            if (Data.SelectedActive != null)
                FilterQuota = FilterQuota.Where(Quota => Quota.Active == Data.SelectedActive);
            SetupManageQuotationsGridBData SearchedManageQuotations;
            SearchedManageQuotations = new SetupManageQuotationsGridBData();
            SearchedManageQuotations.Rows = new List<SetupManageQuotationsGridRowBData>();
            SearchedManageQuotations.Rows = (from Quota in FilterQuota
                                             orderby Quota.QuotaID
                                             select new SetupManageQuotationsGridRowBData()
                                            {
                                                QuotaID = Quota.QuotaID,
                                                QuotaCode = Quota.QuotaCode,
                                                QuotaName = Quota.QuotaName,
                                                Confirmed = Quota.Accepted,
                                                Active = Quota.Active,
                                            }).Take(Data.PageNum * SetupManageQuotationsGridBData.PageCount).ToList();
            SearchedManageQuotations.Confirmed = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            SearchedManageQuotations.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return SearchedManageQuotations;
        }
        public ErrorBInfo SaveManageQuotationDetailsData(SetupManageQuotationsGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> QuotaIDs = Data.Rows.Select(Quota => Quota.QuotaID).ToList();
                var SaveEntityQuotas = _context.tblQuotations.Where(Quota => QuotaIDs.Contains(Quota.QuotaID)).ToList();
                foreach (var Quota in SaveEntityQuotas)
                {
                    var SaveData = Data.Rows.Where(data => data.QuotaID == Quota.QuotaID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        Quota.Accepted = SaveData.Confirmed;
                        Quota.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region ManageQuotationGroupRepository
        public SetupManageQuotationGroupsGridBData LoadManageQuotationGroups(int PageNumber)
        {
            SetupManageQuotationGroupsGridBData QuotationGroups = new SetupManageQuotationGroupsGridBData();
            QuotationGroups.Rows = new List<SetupManageQuotationGroupsGridRowBData>();
            QuotationGroups.Rows = (from grp in _context.tblGroups
                                    from qt in _context.tblQuotations
                                    where grp.IsPushed != true
                                    && grp.QuotaID == qt.QuotaID
                                    && qt.Active == true
                                    orderby grp.QuotaID
                                    select new SetupManageQuotationGroupsGridRowBData()
                                    {
                                        QuotaID = grp.QuotaID,
                                        GroupID = grp.GroupID,
                                        GroupCode = grp.GroupCode,
                                        GroupName = grp.GroupName,
                                        SelStatusID = grp.StatusID,
                                    }).Take(PageNumber * SetupManageQuotationGroupsGridBData.PageCount).ToList();
            QuotationGroups.GroupStatus = (from GrSt in _context.tblGroupStatus
                                            select new
                                            {
                                                Key = GrSt.StatusID,
                                                Value = GrSt.StatusDesc
                                            }).ToDictionary(o => o.Key, o => o.Value);
            return QuotationGroups;
        }
        public SetupManageQuotationGroupsGridBData LoadSearchedManageQuotationGroups(SetupManageQuotationGroupsGridBData Data)
        {
            var FilterGroups = _context.tblGroups.AsQueryable();
            if (Data.QuotaID != null)
                FilterGroups = FilterGroups.Where(grp => grp.QuotaID == Data.QuotaID);
            if (Data.SelectedGroupStatus != null)
                FilterGroups = FilterGroups.Where(grp => grp.StatusID == Data.SelectedGroupStatus);
            SetupManageQuotationGroupsGridBData SearchedManageQuotationGroups;
            SearchedManageQuotationGroups = new SetupManageQuotationGroupsGridBData();
            SearchedManageQuotationGroups.Rows = new List<SetupManageQuotationGroupsGridRowBData>();
            SearchedManageQuotationGroups.Rows = (from grp in FilterGroups
                                                  from quota in _context.tblQuotations
                                                  where
                                                  //grp.IsPushed != true
                                                  grp.QuotaID == quota.QuotaID
                                                  // && quota.Active == true
                                                  orderby grp.QuotaID
                                                  select new SetupManageQuotationGroupsGridRowBData()
                                                  {
                                                      QuotaID = grp.QuotaID,
                                                      GroupID = grp.GroupID,
                                                      GroupCode = grp.GroupCode,
                                                      GroupName = grp.GroupName,
                                                      SelStatusID = grp.StatusID,
                                                  }).Take(Data.PageNum * SetupManageQuotationGroupsGridBData.PageCount).ToList();
            SearchedManageQuotationGroups.GroupStatus = (from GrSt in _context.tblGroupStatus
                                                         select new
                                                         {
                                                             Key = GrSt.StatusID,
                                                             Value = GrSt.StatusDesc
                                                         }).ToDictionary(o => o.Key, o => o.Value);
            return SearchedManageQuotationGroups;
        }
        public ErrorBInfo SaveManageQuotationGroupDetailsData(SetupManageQuotationGroupsGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> GroupIDs = Data.Rows.GroupBy(grp => grp.GroupID).Select(grp => grp.Key).ToList();
                var SaveEntityGroups = _context.tblGroups.Where(grp => GroupIDs.Contains(grp.GroupID)).ToList();
                foreach (var grp in SaveEntityGroups)
                {
                    var SaveData = Data.Rows.Where(data => data.GroupID == grp.GroupID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        if (grp.StatusID != SaveData.SelStatusID)
                            grp.StatusID = SaveData.SelStatusID;
                        if (grp.GroupName != SaveData.GroupName)
                            grp.GroupName = SaveData.GroupName;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region RegionRepository
        public ErrorBInfo CreateRegion(SetupRegionsGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblRegion NewRegion = new tblRegion()
                {
                    CountryID = Data.Rows[0].RegionCountryID,
                    RegionCode = Data.Rows[0].RegionCode,
                    RegionDesc = Data.Rows[0].RegionDescription,
                    Active = Data.Rows[0].Active,
                };
                _context.tblRegions.Add(NewRegion);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupRegionsGridBData LoadRegions(int PageNumber)
        {
            SetupRegionsGridBData Regions = new SetupRegionsGridBData();
            if (PageNumber == -1)
            {
                Regions.Country = (from cnt in _context.tblCountries
                                   where cnt.Active == true
                                   orderby cnt.OrderNum
                                   select new
                                   {
                                       Key = cnt.CountryID,
                                       Value = cnt.CountryName,
                                   }).ToDictionary(o => o.Key, o => o.Value);
                return Regions;
            }
            Regions.Rows = new List<SetupRegionsGridRowBData>();
            Regions.Rows = (from Region in _context.tblRegions
                            orderby Region.RegionCode
                            select new SetupRegionsGridRowBData()
                            {
                                RegionID = Region.RegionID,
                                RegionCountryID = Region.CountryID,
                                RegionCode = Region.RegionCode,
                                RegionDescription = Region.RegionDesc,
                                Active = Region.Active,
                            }).Take(PageNumber * SetupRegionsGridBData.PageCount).ToList();
            Regions.Country = (from cnt in _context.tblCountries
                                       where cnt.Active == true
                                       orderby cnt.OrderNum
                                       select new
                                       {
                                           Key = cnt.CountryID,
                                           Value = cnt.CountryName,
                                       }).ToDictionary(o => o.Key, o => o.Value);
            Regions.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return Regions;
        }
        public ErrorBInfo DeleteRegionById(int RegionID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var Region = _context.tblRegions.Where(reg => reg.RegionID == RegionID).SingleOrDefault();
            try
            {
                _context.tblRegions.Remove(Region);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This Region already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupRegionsGridBData LoadSearchedRegions(SetupRegionsGridBData Data)
        {
            var FilterRegion = _context.tblRegions.AsQueryable();
            if (Data.RegionCountryID != null && Data.RegionCountryID != 0)
                FilterRegion = FilterRegion.Where(reg => reg.CountryID == Data.RegionCountryID);
            if (Data.RegionCode != null && Data.RegionCode.Length > 0)
                FilterRegion = FilterRegion.Where(Region => Region.RegionCode.Contains(Data.RegionCode.Trim()));
            if (Data.RegionDescription != null && Data.RegionDescription.Length > 0)
                FilterRegion = FilterRegion.Where(Region => Region.RegionDesc.Contains(Data.RegionDescription.Trim()));
            if (Data.SelectedActive != null)
                FilterRegion = FilterRegion.Where(reg => reg.Active == Data.SelectedActive);
            SetupRegionsGridBData SearchedRegions;
            SearchedRegions = new SetupRegionsGridBData();
            SearchedRegions.Rows = new List<SetupRegionsGridRowBData>();
            SearchedRegions.Rows = (from Region in FilterRegion
                                    orderby Region.RegionCode
                                    select new SetupRegionsGridRowBData()
                                    {
                                        RegionID = Region.RegionID,
                                        RegionCountryID = Region.CountryID,
                                        RegionCode = Region.RegionCode,
                                        RegionDescription = Region.RegionDesc,
                                        Active = Region.Active,
                                    }).Take(Data.PageNum * SetupRegionsGridBData.PageCount).ToList();
            SearchedRegions.Country = (from cnt in _context.tblCountries
                                       where cnt.Active == true
                                       orderby cnt.OrderNum
                                       select new
                                       {
                                           Key = cnt.CountryID,
                                           Value = cnt.CountryName,
                                       }).ToDictionary(o => o.Key, o => o.Value);
            SearchedRegions.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return SearchedRegions;
        }
        public ErrorBInfo SaveRegionDetailsData(SetupRegionsGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> RegionIDs = Data.Rows.Select(Region => Region.RegionID).ToList();
                var SaveEntityRegions = _context.tblRegions.Where(Region => RegionIDs.Contains(Region.RegionID)).ToList();
                foreach (var Region in SaveEntityRegions)
                {
                    var SaveData = Data.Rows.Where(data => data.RegionID == Region.RegionID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        Region.RegionCode = SaveData.RegionCode;
                        Region.CountryID = SaveData.RegionCountryID;
                        Region.RegionDesc = SaveData.RegionDescription;
                        Region.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region StateRepository
        public ErrorBInfo CreateState(SetupStatesGridRowBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblState NewState = new tblState()
                {
                    StateID = Data.StateID,
                    StateName = Data.StateName,
                    Active = Data.Active,
                };
                _context.tblStates.Add(NewState);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupStatesGridBData LoadStates(int PageNumber)
        {
            SetupStatesGridBData States = new SetupStatesGridBData();
            States.Rows = new List<SetupStatesGridRowBData>();
            States.Rows = (from State in _context.tblStates
                           orderby State.StateID
                           select new SetupStatesGridRowBData()
                            {
                                StID = State.StID,
                                StateID = State.StateID,
                                StateName = State.StateName,
                                Active = State.Active,
                            }).Take(PageNumber * SetupRegionsGridBData.PageCount).ToList();
            States.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return States;
        }
        public ErrorBInfo DeleteStateById(int StID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var State = _context.tblStates.Where(state => state.StID == StID).SingleOrDefault();
            try
            {
                _context.tblStates.Remove(State);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This State already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupStatesGridBData LoadSearchedStates(SetupStatesGridBData Data)
        {
            var FilterState = _context.tblStates.AsQueryable();
            if (Data.StateID != null && Data.StateID.Length > 0)
                FilterState = FilterState.Where(State => State.StateID.Contains(Data.StateID.Trim()));
            if (Data.StateName != null && Data.StateName.Length > 0)
                FilterState = FilterState.Where(State => State.StateName.Contains(Data.StateName.Trim()));
            if (Data.SelectedActive != null)
                FilterState = FilterState.Where(ven => ven.Active == Data.SelectedActive);
            SetupStatesGridBData SearchedStates;
            SearchedStates = new SetupStatesGridBData();
            SearchedStates.Rows = new List<SetupStatesGridRowBData>();
            SearchedStates.Rows = (from State in FilterState
                                   orderby State.StateID
                                   select new SetupStatesGridRowBData()
                                    {
                                        StID = State.StID,
                                        StateID = State.StateID,
                                        StateName = State.StateName,
                                        Active = State.Active,
                                    }).Take(Data.PageNum * SetupRegionsGridBData.PageCount).ToList();
            SearchedStates.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return SearchedStates;
        }
        public ErrorBInfo SaveStateDetailsData(SetupStatesGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> StIDs = Data.Rows.Select(State => State.StID).ToList();
                var SaveEntityStates = _context.tblStates.Where(State => StIDs.Contains(State.StID)).ToList();
                foreach (var State in SaveEntityStates)
                {
                    var SaveData = Data.Rows.Where(data => data.StID == State.StID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        State.StateID = SaveData.StateID;
                        State.StateName = SaveData.StateName;
                        State.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region VendorTypeRepository
        public ErrorBInfo CreateVendorType(SetupVendorTypesGridRowBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblVendorType NewVendorType = new tblVendorType()
                {
                    VendorTypeCode = Data.VendorTypeCode,
                    VendorTypeDesc = Data.VendorTypeDescription,
                    Active = Data.Active,
                };
                _context.tblVendorTypes.Add(NewVendorType);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupVendorTypesGridBData LoadVendorTypes(int PageNumber)
        {
            SetupVendorTypesGridBData VendorTypes = new SetupVendorTypesGridBData();
            VendorTypes.Rows = new List<SetupVendorTypesGridRowBData>();
            VendorTypes.Rows = (from VendorType in _context.tblVendorTypes
                                orderby VendorType.VendorTypeCode
                                select new SetupVendorTypesGridRowBData()
                            {
                                VendorTypeID = VendorType.VendorTypeID,
                                VendorTypeCode = VendorType.VendorTypeCode,
                                VendorTypeDescription = VendorType.VendorTypeDesc,
                                Active = VendorType.Active,
                            }).Take(PageNumber * SetupVendorTypesGridBData.PageCount).ToList();
            VendorTypes.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return VendorTypes;
        }
        public ErrorBInfo DeleteVendorTypeById(int VendorTypeID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                var VendorType = _context.tblVendorTypes.Where(VType => VType.VendorTypeID == VendorTypeID).SingleOrDefault();
                _context.tblVendorTypes.Remove(VendorType);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This Vendor Type already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupVendorTypesGridBData LoadSearchedVendorTypes(SetupVendorTypesGridBData Data)
        {
            var FilterVendorType = _context.tblVendorTypes.AsQueryable();
            if (Data.VendorTypeCode != null && Data.VendorTypeCode.Length > 0)
                FilterVendorType = FilterVendorType.Where(VendorType => VendorType.VendorTypeCode.Contains(Data.VendorTypeCode.Trim()));
            if (Data.VendorTypeDescription != null && Data.VendorTypeDescription.Length > 0)
                FilterVendorType = FilterVendorType.Where(VendorType => VendorType.VendorTypeDesc.Contains(Data.VendorTypeDescription.Trim()));
            if (Data.SelectedActive != null)
                FilterVendorType = FilterVendorType.Where(ven => ven.Active == Data.SelectedActive);
            SetupVendorTypesGridBData SearchedVendorTypes;
            SearchedVendorTypes = new SetupVendorTypesGridBData();
            SearchedVendorTypes.Rows = new List<SetupVendorTypesGridRowBData>();
            SearchedVendorTypes.Rows = (from VendorType in FilterVendorType
                                        orderby VendorType.VendorTypeCode
                                        select new SetupVendorTypesGridRowBData()
                                    {
                                        VendorTypeID = VendorType.VendorTypeID,
                                        VendorTypeCode = VendorType.VendorTypeCode,
                                        VendorTypeDescription = VendorType.VendorTypeDesc,
                                        Active = VendorType.Active,
                                    }).Take(Data.PageNum * SetupVendorTypesGridBData.PageCount).ToList();
            SearchedVendorTypes.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return SearchedVendorTypes;
        }
        public ErrorBInfo SaveVendorTypeDetailsData(SetupVendorTypesGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> VendorTypeIDs = Data.Rows.Select(VendorType => VendorType.VendorTypeID).ToList();
                var SaveEntityVendorTypes = _context.tblVendorTypes.Where(VendorType => VendorTypeIDs.Contains(VendorType.VendorTypeID)).ToList();
                foreach (var VendorType in SaveEntityVendorTypes)
                {
                    var SaveData = Data.Rows.Where(data => data.VendorTypeID == VendorType.VendorTypeID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        VendorType.VendorTypeCode = SaveData.VendorTypeCode;
                        VendorType.VendorTypeDesc = SaveData.VendorTypeDescription;
                        VendorType.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region DetailTypeRepository
        public ErrorBInfo CreateDetailType(SetupDetailTypesGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblDetailType NewDetailType = new tblDetailType()
                {
                    VendorTypeID = Data.Rows[0].DetailVendorTypeID,
                    DetailTypeDesc = Data.Rows[0].DetailTypeDescription,
                    Active = Data.Rows[0].Active,
                };
                _context.tblDetailTypes.Add(NewDetailType);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupDetailTypesGridBData LoadDetailTypes(int PageNumber)
        {
            SetupDetailTypesGridBData DetailTypes = new SetupDetailTypesGridBData();
            if (PageNumber == -1)
            {
                DetailTypes.VendorTypeID = (from vtype in _context.tblVendorTypes
                                            where vtype.Active == true
                                            select new
                                            {
                                                Key = vtype.VendorTypeID,
                                                Value = vtype.VendorTypeCode,
                                            }).ToDictionary(o => o.Key, o => o.Value);
                return DetailTypes;
            }
            DetailTypes.Rows = new List<SetupDetailTypesGridRowBData>();
            DetailTypes.Rows = (from DetailType in _context.tblDetailTypes
                                from vtype in _context.tblVendorTypes
                                where vtype.VendorTypeID == DetailType.VendorTypeID
                                && vtype.Active == true
                                orderby DetailType.VendorTypeID
                                select new SetupDetailTypesGridRowBData()
                                {
                                    DetailTypeID = DetailType.DetailTypeID,
                                    DetailVendorTypeID = DetailType.VendorTypeID,
                                    DetailTypeDescription = DetailType.DetailTypeDesc,
                                    Active = DetailType.Active,
                                }).Take(PageNumber * SetupVendorTypesGridBData.PageCount).ToList();
            DetailTypes.VendorTypeID = (from vtype in _context.tblVendorTypes
                                        where vtype.Active == true
                                        select new
                                        {
                                            Key = vtype.VendorTypeID,
                                            Value = vtype.VendorTypeCode,
                                        }).ToDictionary(o => o.Key, o => o.Value);
            DetailTypes.Active = new Dictionary<bool, string>
                 {
                             {true,"Yes"},
                             {false,"No"},
                 };
            return DetailTypes;
        }
        public ErrorBInfo DeleteDetailTypeById(int DetailTypeID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var DetailType = _context.tblDetailTypes.Where(DType => DType.DetailTypeID == DetailTypeID).SingleOrDefault();
            try
            {
                _context.tblDetailTypes.Remove(DetailType);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This Detail Type already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }

        public SetupDetailTypesGridBData LoadSearchedDetailTypes(SetupDetailTypesGridBData Data)
        {
            var FilterDetailType = _context.tblDetailTypes.AsQueryable();
            if (Data.DetailVendorTypeID != 0)
                FilterDetailType = FilterDetailType.Where(DetailType => DetailType.VendorTypeID == Data.DetailVendorTypeID);
            if (Data.DetailTypeDescription != null && Data.DetailTypeDescription.Length > 0)
                FilterDetailType = FilterDetailType.Where(DetailType => DetailType.DetailTypeDesc.Contains(Data.DetailTypeDescription.Trim()));
            if (Data.SelectedActive != null)
                FilterDetailType = FilterDetailType.Where(ven => ven.Active == Data.SelectedActive);
            SetupDetailTypesGridBData SearchedDetailTypes;
            SearchedDetailTypes = new SetupDetailTypesGridBData();
            SearchedDetailTypes.Rows = new List<SetupDetailTypesGridRowBData>();
            SearchedDetailTypes.Rows = (from DetailType in FilterDetailType
                                        from vtype in _context.tblVendorTypes
                                        where vtype.VendorTypeID == DetailType.VendorTypeID
                                        && vtype.Active == true
                                        orderby DetailType.VendorTypeID
                                        select new SetupDetailTypesGridRowBData()
                                        {
                                            DetailVendorTypeID = DetailType.VendorTypeID,
                                            DetailTypeID = DetailType.DetailTypeID,
                                            DetailTypeDescription = DetailType.DetailTypeDesc,
                                            Active = DetailType.Active,
                                        }).Take(Data.PageNum * SetupDetailTypesGridBData.PageCount).ToList();
            SearchedDetailTypes.VendorTypeID = (from vtype in _context.tblVendorTypes
                                                where vtype.Active == true
                                                select new
                                                {
                                                    Key = vtype.VendorTypeID,
                                                    Value = vtype.VendorTypeCode,
                                                }).ToDictionary(o => o.Key, o => o.Value);
            SearchedDetailTypes.Active = new Dictionary<bool, string>
                 {
                             {true,"Yes"},
                             {false,"No"},
                 };
            return SearchedDetailTypes;
        }
        public ErrorBInfo SaveDetailTypeDetailsData(SetupDetailTypesGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> DetailTypeIDs = Data.Rows.Select(DetailType => DetailType.DetailTypeID).ToList();
                var SaveEntityDetailTypes = _context.tblDetailTypes.Where(DetailType => DetailTypeIDs.Contains(DetailType.DetailTypeID)).ToList();
                foreach (var DetailType in SaveEntityDetailTypes)
                {
                    var SaveData = Data.Rows.Where(data => data.DetailTypeID == DetailType.DetailTypeID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        DetailType.VendorTypeID = SaveData.DetailVendorTypeID;
                        DetailType.DetailTypeDesc = SaveData.DetailTypeDescription;
                        DetailType.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region PaymentTypeRepository
        public ErrorBInfo CreatePaymentType(SetupPaymentTypesGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblPayType NewPaymentType = new tblPayType()
                {
                    BankID = Data.Rows[0].PaymentTypeBankID,
                    PayTypeCode = Data.Rows[0].PaymentTypeCode,
                    PayTypeDesc = Data.Rows[0].PaymentTypeDescription,
                    Active = Data.Rows[0].Active,
                };
                _context.tblPayTypes.Add(NewPaymentType);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupPaymentTypesGridBData LoadPaymentTypes(int PageNumber)
        {
            SetupPaymentTypesGridBData PaymentTypes = new SetupPaymentTypesGridBData();
            if (PageNumber == -1)
            {
                PaymentTypes.BankID = (from bank in _context.tblBanks
                                       where bank.Active == true
                                       select new
                                       {
                                           Key = bank.BankID,
                                           Value = bank.BankCode,
                                       }).ToDictionary(o => o.Key, o => o.Value);
                return PaymentTypes;
            }
            PaymentTypes.Rows = new List<SetupPaymentTypesGridRowBData>();
            PaymentTypes.Rows = (from PaymentType in _context.tblPayTypes
                                 from bank in _context.tblBanks
                                 where bank.Active == true
                                 && bank.BankID == PaymentType.BankID
                                 orderby PaymentType.PayTypeCode
                                 select new SetupPaymentTypesGridRowBData()
                                {
                                    PaymentTypeID = PaymentType.PayTypeID,
                                    PaymentTypeBankID = PaymentType.BankID,
                                    PaymentTypeCode = PaymentType.PayTypeCode,
                                    PaymentTypeDescription = PaymentType.PayTypeDesc,
                                    Active = PaymentType.Active,
                                }).Take(PageNumber * SetupPaymentTypesGridBData.PageCount).ToList();
            PaymentTypes.BankID = (from bank in _context.tblBanks
                                   where bank.Active == true
                                   select new
                                   {
                                       Key = bank.BankID,
                                       Value = bank.BankCode,
                                   }).ToDictionary(o => o.Key, o => o.Value);
            PaymentTypes.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return PaymentTypes;
        }
        public ErrorBInfo DeletePaymentTypeById(int PaymentTypeID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var PayType = _context.tblPayTypes.Where(PType => PType.PayTypeID == PaymentTypeID).SingleOrDefault();
            try
            {
                _context.tblPayTypes.Remove(PayType);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This Payment Type already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupPaymentTypesGridBData LoadSearchedPaymentTypes(SetupPaymentTypesGridBData Data)
        {
            var Filter = _context.tblPayTypes.AsQueryable();
            if (Data.PaymentTypeBankID != null && Data.PaymentTypeBankID != 0)
                Filter = Filter.Where(PaymentType => PaymentType.BankID == Data.PaymentTypeBankID);
            if (Data.PaymentTypeDescription != null && Data.PaymentTypeDescription.Length > 0)
                Filter = Filter.Where(PaymentType => PaymentType.PayTypeDesc.Contains(Data.PaymentTypeDescription.Trim()));
            if (Data.PaymentTypeCode != null && Data.PaymentTypeCode.Length > 0)
                Filter = Filter.Where(PaymentType => PaymentType.PayTypeCode.Contains(Data.PaymentTypeCode.Trim()));
            if (Data.SelectedActive != null)
                Filter = Filter.Where(PaymentType => PaymentType.Active == Data.SelectedActive);
            SetupPaymentTypesGridBData Searched;
            Searched = new SetupPaymentTypesGridBData();
            Searched.Rows = new List<SetupPaymentTypesGridRowBData>();
            Searched.Rows = (from PaymentType in Filter
                             from bank in _context.tblBanks
                             where bank.Active == true
                             && bank.BankID == PaymentType.BankID
                             orderby PaymentType.PayTypeCode
                             select new SetupPaymentTypesGridRowBData()
                                        {
                                            PaymentTypeBankID = PaymentType.BankID,
                                            PaymentTypeID = PaymentType.PayTypeID,
                                            PaymentTypeCode = PaymentType.PayTypeCode,
                                            PaymentTypeDescription = PaymentType.PayTypeDesc,
                                            Active = PaymentType.Active,
                                        }).Take(Data.PageNum * SetupPaymentTypesGridBData.PageCount).ToList();
            Searched.BankID = (from bank in _context.tblBanks
                               where bank.Active == true
                               select new
                               {
                                   Key = bank.BankID,
                                   Value = bank.BankCode,
                               }).ToDictionary(o => o.Key, o => o.Value);
            Searched.Active = new Dictionary<bool, string>
                 {
                             {true,"Yes"},
                             {false,"No"},
                 };
            return Searched;
        }
        public ErrorBInfo SavePaymentTypeDetailsData(SetupPaymentTypesGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> PaymentTypeIDs = Data.Rows.Select(PaymentType => PaymentType.PaymentTypeID).ToList();
                var SaveEntityPaymentTypes = _context.tblPayTypes.Where(PaymentType => PaymentTypeIDs.Contains(PaymentType.PayTypeID)).ToList();
                foreach (var PaymentType in SaveEntityPaymentTypes)
                {
                    var SaveData = Data.Rows.Where(data => data.PaymentTypeID == PaymentType.PayTypeID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        PaymentType.BankID = SaveData.PaymentTypeBankID;
                        PaymentType.PayTypeCode = SaveData.PaymentTypeCode;
                        PaymentType.PayTypeDesc = SaveData.PaymentTypeDescription;
                        PaymentType.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region CountryRepository
        public ErrorBInfo CreateCountry(SetupCountriesGridRowBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblCountry NewCountry = new tblCountry()
                {
                    CountryCode = Data.CountryCode,
                    CountryName = Data.CountryName,
                    Nationality = Data.Nationality,
                    OrderNum = 3,
                    Active = Data.Active,
                };
                _context.tblCountries.Add(NewCountry);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public ErrorBInfo DeleteCountryById(int CountryID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var Country = _context.tblCountries.Where(cnt => cnt.CountryID == CountryID).SingleOrDefault();
            try
            {
                _context.tblCountries.Remove(Country);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This Country already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }

        public SetupCountriesGridBData LoadCountries(int PageNumber)
        {
            SetupCountriesGridBData Data = new SetupCountriesGridBData();
            Data.Rows = new List<SetupCountriesGridRowBData>();
            Data.Rows = (from Country in _context.tblCountries
                         orderby Country.CountryCode
                         select new SetupCountriesGridRowBData()
                            {
                                CountryID = Country.CountryID,
                                CountryCode = Country.CountryCode,
                                CountryName = Country.CountryName,
                                Nationality = Country.Nationality,
                                Active = Country.Active,
                            }).Take(PageNumber * SetupCountriesGridBData.PageCount).ToList();
            Data.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return Data;
        }
        public SetupCountriesGridBData LoadSearchedCountries(SetupCountriesGridBData Data)
        {
            var Filter = _context.tblCountries.AsQueryable();
            if (Data.CountryCode != null && Data.CountryCode.Length > 0)
                Filter = Filter.Where(Country => Country.CountryCode.Contains(Data.CountryCode.Trim()));
            if (Data.CountryName != null && Data.CountryName.Length > 0)
                Filter = Filter.Where(Country => Country.CountryName.Contains(Data.CountryName.Trim()));
            if (Data.Nationality != null && Data.Nationality.Length > 0)
                Filter = Filter.Where(Country => Country.Nationality.Contains(Data.Nationality.Trim()));
            if (Data.SelectedActive != null)
                Filter = Filter.Where(Country => Country.Active == Data.SelectedActive);
            SetupCountriesGridBData Searched;
            Searched = new SetupCountriesGridBData();
            Searched.Rows = new List<SetupCountriesGridRowBData>();
            Searched.Rows = (from Country in Filter
                             orderby Country.CountryCode
                             select new SetupCountriesGridRowBData()
                                    {
                                        CountryID = Country.CountryID,
                                        CountryCode = Country.CountryCode,
                                        CountryName = Country.CountryName,
                                        Nationality = Country.Nationality,
                                        Active = Country.Active,
                                    }).Take(Data.PageNum * SetupCountriesGridBData.PageCount).ToList();
            Searched.Active = new Dictionary<bool, string>
                 {
                             {true,"Yes"},
                             {false,"No"},
                 };
            return Searched;
        }
        public ErrorBInfo SaveCountryDetailsData(SetupCountriesGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> CountryIDs = Data.Rows.Select(Country => Country.CountryID).ToList();
                var SaveEntityCountries = _context.tblCountries.Where(Country => CountryIDs.Contains(Country.CountryID)).ToList();
                foreach (var Country in SaveEntityCountries)
                {
                    var SaveData = Data.Rows.Where(data => data.CountryID == Country.CountryID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        Country.CountryCode = SaveData.CountryCode;
                        Country.CountryName = SaveData.CountryName;
                        Country.Nationality = SaveData.Nationality;
                        Country.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region AreaRepository
        public ErrorBInfo CreateArea(SetupAreasGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblArea NewAreaType = new tblArea()
                {
                    RegionID = Data.Rows[0].AreaRegionID,
                    AreaCode = Data.Rows[0].AreaCode,
                    AreaDesc = Data.Rows[0].AreaDescription,
                    Active = Data.Rows[0].Active,
                };
                _context.tblAreas.Add(NewAreaType);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupAreasGridBData LoadAreas(int PageNumber)
        {
            SetupAreasGridBData Areas = new SetupAreasGridBData();
            if (PageNumber == -1)
            {
                Areas.RegionID = (from Region in _context.tblRegions
                                  where Region.Active == true
                                  orderby Region.RegionDesc
                                  select new
                                  {
                                      Key = Region.RegionID,
                                      Value = Region.RegionDesc,
                                  }).ToDictionary(o => o.Key, o => o.Value);
                return Areas;
            }
            Areas.Rows = new List<SetupAreasGridRowBData>();
            Areas.Rows = (from Area in _context.tblAreas
                          from Region in _context.tblRegions
                          where Region.Active == true
                          && Region.RegionID == Area.RegionID
                          orderby Area.AreaCode
                          select new SetupAreasGridRowBData()
                                 {
                                     AreaID = Area.AreaID,
                                     AreaRegionID = Area.RegionID,
                                     AreaCode = Area.AreaCode,
                                     AreaDescription = Area.AreaDesc,
                                     Active = Area.Active,
                                 }).Take(PageNumber * SetupAreasGridBData.PageCount).ToList();
            Areas.RegionID = (from Region in _context.tblRegions
                              where Region.Active == true
                              orderby Region.RegionDesc
                              select new
                              {
                                  Key = Region.RegionID,
                                  Value = Region.RegionDesc,
                              }).ToDictionary(o => o.Key, o => o.Value);
            Areas.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return Areas;
        }
        public ErrorBInfo DeleteAreaById(int AreaID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var Area = _context.tblAreas.Where(area => area.AreaID == AreaID).SingleOrDefault();
            try
            {
                _context.tblAreas.Remove(Area);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This Area already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupAreasGridBData LoadSearchedAreas(SetupAreasGridBData Data)
        {
            var Filter = _context.tblAreas.AsQueryable();
            if (Data.AreaRegionID != null && Data.AreaRegionID != 0)
                Filter = Filter.Where(Area => Area.RegionID == Data.AreaRegionID);
            if (Data.AreaDescription != null && Data.AreaDescription.Length > 0)
                Filter = Filter.Where(Area => Area.AreaDesc.Contains(Data.AreaDescription.Trim()));
            if (Data.AreaCode != null && Data.AreaCode.Length > 0)
                Filter = Filter.Where(Area => Area.AreaCode.Contains(Data.AreaCode.Trim()));
            if (Data.SelectedActive != null)
                Filter = Filter.Where(Area => Area.Active == Data.SelectedActive);
            SetupAreasGridBData Searched;
            Searched = new SetupAreasGridBData();
            Searched.Rows = new List<SetupAreasGridRowBData>();
            Searched.Rows = (from Area in Filter
                             from Region in _context.tblRegions
                             where Region.Active == true
                             && Region.RegionID == Area.RegionID
                             orderby Area.AreaCode
                             select new SetupAreasGridRowBData()
                             {
                                 AreaID = Area.AreaID,
                                 AreaRegionID = Area.RegionID,
                                 AreaCode = Area.AreaCode,
                                 AreaDescription = Area.AreaDesc,
                                 Active = Area.Active,
                             }).Take(Data.PageNum * SetupAreasGridBData.PageCount).ToList();
            Searched.RegionID = (from Region in _context.tblRegions
                                 where Region.Active == true
                                 orderby Region.RegionDesc
                                 select new
                                 {
                                     Key = Region.RegionID,
                                     Value = Region.RegionDesc,
                                 }).ToDictionary(o => o.Key, o => o.Value);
            Searched.Active = new Dictionary<bool, string>
                              {
                                          {true,"Yes"},
                                          {false,"No"},
                              };
            return Searched;
        }
        public ErrorBInfo SaveAreaDetailsData(SetupAreasGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> AreaIDs = Data.Rows.Select(Area => Area.AreaID).ToList();
                var SaveEntityAreas = _context.tblAreas.Where(Area => AreaIDs.Contains(Area.AreaID)).ToList();
                foreach (var Area in SaveEntityAreas)
                {
                    var SaveData = Data.Rows.Where(data => data.AreaID == Area.AreaID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        Area.RegionID = SaveData.AreaRegionID;
                        Area.AreaCode = SaveData.AreaCode;
                        Area.AreaDesc = SaveData.AreaDescription;
                        Area.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region ServiceRepository
        public ErrorBInfo CreateService(SetupServicesGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblService NewService = new tblService()
                {
                    ServiceTypeID = Data.Rows[0].ServiceTypeID,
                    ServiceCode = Data.Rows[0].ServiceCode,
                    ServiceDesc = Data.Rows[0].ServiceDescription,
                    Active = Data.Rows[0].Active,
                };
                _context.tblServices.Add(NewService);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupServicesGridBData LoadServices(int PageNumber)
        {
            SetupServicesGridBData Services = new SetupServicesGridBData();
            if (PageNumber == -1)
            {
                Services.ServiceTypeID = (from ServiceType in _context.tblServiceTypes
                                          from VendorType in _context.tblVendorTypes
                                          where ServiceType.VendorTypeID == VendorType.VendorTypeID
                                          && ServiceType.Active == true
                                          && VendorType.Active == true
                                          select new
                                          {
                                              Key = ServiceType.ServiceTypeID,
                                              Value =
                                              (ServiceType.ServiceTypeCode != null ? ServiceType.ServiceTypeCode : "")
                                              + " " +
                                              (VendorType.VendorTypeCode != null ? VendorType.VendorTypeCode : "")
                                              + " " +
                                              (ServiceType.ServiceTypeDesc != null ? ServiceType.ServiceTypeDesc : ""),
                                          }).ToDictionary(o => o.Key, o => o.Value);
                Services.VendorTypes = (from VTypes in _context.tblVendorTypes
                                        select new { Key = VTypes.VendorTypeID, Value = VTypes.VendorTypeCode }).ToDictionary(o => o.Key, o => o.Value);
                                           
                return Services;
            }
            Services.Rows = new List<SetupServicesGridRowBData>();
            Services.Rows = (from Service in _context.tblServices
                             from ServiceType in _context.tblServiceTypes
                             from VendorType in _context.tblVendorTypes
                             where ServiceType.VendorTypeID == VendorType.VendorTypeID
                             && ServiceType.Active == true
                             && VendorType.Active == true
                             && Service.ServiceTypeID == ServiceType.ServiceTypeID
                             orderby Service.ServiceCode
                             select new SetupServicesGridRowBData()
                             {
                                 ServiceID = Service.ServiceID,
                                 ServiceTypeID = Service.ServiceTypeID,
                                 ServiceCode = Service.ServiceCode,
                                 ServiceDescription = Service.ServiceDesc,
                                 Active = Service.Active,
                             }).Take(PageNumber * SetupServicesGridBData.PageCount).ToList();
            Services.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            Services.ServiceTypeID = (from ServiceType in _context.tblServiceTypes
                                      from VendorType in _context.tblVendorTypes
                                      where ServiceType.VendorTypeID == VendorType.VendorTypeID
                                      && ServiceType.Active == true
                                      && VendorType.Active == true
                                      select new
                                      {
                                          Key = ServiceType.ServiceTypeID,
                                          Value =
                                          (ServiceType.ServiceTypeCode != null ? ServiceType.ServiceTypeCode : "")
                                          + " " +
                                          (VendorType.VendorTypeCode != null ? VendorType.VendorTypeCode : "")
                                          + " " +
                                          (ServiceType.ServiceTypeDesc != null ? ServiceType.ServiceTypeDesc : ""),
                                      }).ToDictionary(o => o.Key, o => o.Value);
            Services.VendorTypes = (from VTypes in _context.tblVendorTypes
                                    select new { Key = VTypes.VendorTypeID, Value = VTypes.VendorTypeCode }).ToDictionary(o => o.Key, o => o.Value);
            return Services;
        }
        public ErrorBInfo DeleteServiceById(int ServiceID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var Service = _context.tblServices.Where(service => service.ServiceID == ServiceID).SingleOrDefault();
            try
            {
                _context.tblServices.Remove(Service);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This Service already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupServicesGridBData LoadSearchedServices(SetupServicesGridBData Data)
        {
            var Filter = _context.tblServices.AsQueryable();
            var ServiceTypes = _context.tblServiceTypes.AsQueryable();
            if (Data.VendorTypeID != 0)
                ServiceTypes = ServiceTypes.Where(SrvType => SrvType.VendorTypeID == Data.VendorTypeID);
            if (Data.ServiceServiceTypeID != 0)
                Filter = Filter.Where(Service => Service.ServiceTypeID == Data.ServiceServiceTypeID);
            if (Data.ServiceDescription != null && Data.ServiceDescription.Length > 0)
                Filter = Filter.Where(Service => Service.ServiceDesc.Contains(Data.ServiceDescription.Trim()));
            if (Data.ServiceCode != null && Data.ServiceCode.Length > 0)
                Filter = Filter.Where(Service => Service.ServiceCode.Contains(Data.ServiceCode.Trim()));
            if (Data.SelectedActive != null)
                Filter = Filter.Where(Service => Service.Active == Data.SelectedActive);
            SetupServicesGridBData Searched;
            Searched = new SetupServicesGridBData();
            Searched.Rows = new List<SetupServicesGridRowBData>();
            Searched.Rows = (from Service in Filter
                             from ServiceType in ServiceTypes
                             from VendorType in _context.tblVendorTypes
                             where ServiceType.VendorTypeID == VendorType.VendorTypeID
                             && ServiceType.Active == true
                             && VendorType.Active == true
                             && Service.ServiceTypeID == ServiceType.ServiceTypeID
                             orderby Service.ServiceCode
                             select new SetupServicesGridRowBData()
                             {
                                 ServiceID = Service.ServiceID,
                                 ServiceTypeID = Service.ServiceTypeID,
                                 ServiceCode = Service.ServiceCode,
                                 ServiceDescription = Service.ServiceDesc,
                                 Active = Service.Active,
                             }).Take(Data.PageNum * SetupServicesGridBData.PageCount).ToList();
            Searched.ServiceTypeID = (from ServiceType in _context.tblServiceTypes
                                      from VendorType in _context.tblVendorTypes
                                      where ServiceType.VendorTypeID == VendorType.VendorTypeID
                                      && ServiceType.Active == true
                                      && VendorType.Active == true
                                      select new
                                      {
                                          Key = ServiceType.ServiceTypeID,
                                          Value =
                                          (ServiceType.ServiceTypeCode != null ? ServiceType.ServiceTypeCode : "")
                                          + " " +
                                          (VendorType.VendorTypeCode != null ? VendorType.VendorTypeCode : "")
                                          + " " +
                                          (ServiceType.ServiceTypeDesc != null ? ServiceType.ServiceTypeDesc : ""),
                                      }).ToDictionary(o => o.Key, o => o.Value);
            Searched.Active = new Dictionary<bool, string>
                 {
                             {true,"Yes"},
                             {false,"No"},
                 };
            Searched.VendorTypes = (from VTypes in _context.tblVendorTypes
                                    select new { Key = VTypes.VendorTypeID, Value = VTypes.VendorTypeCode }).ToDictionary(o => o.Key, o => o.Value);
            return Searched;
        }
        public ErrorBInfo SaveServiceDetailsData(SetupServicesGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            using (var transaction = _context.Database.BeginTransaction(IsolationLevel.Serializable))
            {
                var ServiceIDs = Data.Rows.Select(Service => Service.ServiceID).ToList();
                var SaveEntityServices = _context.tblServices.Where(Service => ServiceIDs.Contains(Service.ServiceID)).ToList();
                foreach (var Service in SaveEntityServices)
                {
                    var SaveData = Data.Rows.Where(data => data.ServiceID == Service.ServiceID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        if (Service.ServiceTypeID != SaveData.ServiceTypeID)
                            Service.ServiceTypeID = SaveData.ServiceTypeID;
                        if (Service.ServiceCode != SaveData.ServiceCode)
                            Service.ServiceCode = SaveData.ServiceCode;
                        if (Service.ServiceDesc != SaveData.ServiceDescription)
                            Service.ServiceDesc = SaveData.ServiceDescription;
                        if (Service.Active != SaveData.Active)
                            Service.Active = SaveData.Active;
                    }
                }
                try
                {
                    _context.SaveChanges();
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    Error.Message = ex.Message;
                    Error.errorCode = 1099;
                    logger.Error(ex.ToMessageAndCompleteStacktrace());
                    transaction.Rollback();
                }
            }
            return Error;
        }
        #endregion
        #region ServiceTypeRepository
        public ErrorBInfo CreateServiceType(SetupServiceTypesGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblServiceType NewServiceType = new tblServiceType()
                {
                    VendorTypeID = Data.Rows[0].VendorTypeID,
                    Per = Data.Rows[0].Per,
                    ServiceTypeCode = Data.Rows[0].ServiceTypeCode,
                    ServiceTypeDesc = Data.Rows[0].ServiceTypeDescription,
                    Active = Data.Rows[0].Active,
                };
                _context.tblServiceTypes.Add(NewServiceType);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupServiceTypesGridBData LoadServiceTypes(int PageNumber)
        {
            SetupServiceTypesGridBData ServiceTypes = new SetupServiceTypesGridBData();
            if (PageNumber == -1)
            {
                ServiceTypes.VendorTypeID = (from VendorType in _context.tblVendorTypes
                                             where VendorType.Active == true
                                             select new
                                             {
                                                 Key = VendorType.VendorTypeID,
                                                 Value = VendorType.VendorTypeCode,
                                             }).ToDictionary(o => o.Key, o => o.Value);
                ServiceTypes.Per = new Dictionary<int, string>
                {
                    {1,"Per"},
                    {2,"Quantity"},
                };
                return ServiceTypes;
            }
            ServiceTypes.Rows = new List<SetupServiceTypesGridRowBData>();
            ServiceTypes.Rows = (from ServiceType in _context.tblServiceTypes
                                 from VendorType in _context.tblVendorTypes
                                 where VendorType.Active == true
                                 && ServiceType.VendorTypeID == VendorType.VendorTypeID
                                 orderby ServiceType.ServiceTypeCode
                                 select new SetupServiceTypesGridRowBData()
                                 {
                                     ServiceTypeID = ServiceType.ServiceTypeID,
                                     VendorTypeID = ServiceType.VendorTypeID,
                                     ServiceTypeCode = ServiceType.ServiceTypeCode,
                                     ServiceTypeDescription = ServiceType.ServiceTypeDesc,
                                     Per = ServiceType.Per,
                                     Active = ServiceType.Active,
                                 }).Take(PageNumber * SetupServicesGridBData.PageCount).ToList();
            ServiceTypes.VendorTypeID = (from VendorType in _context.tblVendorTypes
                                         where VendorType.Active == true
                                         select new
                                         {
                                             Key = VendorType.VendorTypeID,
                                             Value = VendorType.VendorTypeCode,
                                         }).ToDictionary(o => o.Key, o => o.Value);
            ServiceTypes.Per = new Dictionary<int, string>
            {
                {1,"Per"},
                {2,"Quantity"},
            };
            ServiceTypes.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return ServiceTypes;
        }
        public ErrorBInfo DeleteServiceTypeById(int ServiceTypeID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                var ServiceType = _context.tblServiceTypes.Where(serviceType => serviceType.ServiceTypeID == ServiceTypeID).SingleOrDefault();
                _context.tblServiceTypes.Remove(ServiceType);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This Service Type already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupServiceTypesGridBData LoadSearchedServiceTypes(SetupServiceTypesGridBData Data)
        {
            var Filter = _context.tblServiceTypes.AsQueryable();
            if (Data.ServiceTypeVendorTypeID != null && Data.ServiceTypeVendorTypeID != 0)
                Filter = Filter.Where(ServiceType => ServiceType.VendorTypeID == Data.ServiceTypeVendorTypeID);
            if (Data.ServiceTypePer != null && Data.ServiceTypePer != 0)
                Filter = Filter.Where(ServiceType => ServiceType.Per == Data.ServiceTypePer);
            if (Data.ServiceTypeDescription != null && Data.ServiceTypeDescription.Length > 0)
                Filter = Filter.Where(Service => Service.ServiceTypeDesc.Contains(Data.ServiceTypeDescription.Trim()));
            if (Data.ServiceTypeCode != null && Data.ServiceTypeCode.Length > 0)
                Filter = Filter.Where(Service => Service.ServiceTypeCode.Contains(Data.ServiceTypeCode.Trim()));
            if (Data.SelectedActive != null)
                Filter = Filter.Where(Service => Service.Active == Data.SelectedActive);
            SetupServiceTypesGridBData Searched;
            Searched = new SetupServiceTypesGridBData();
            Searched.Rows = new List<SetupServiceTypesGridRowBData>();
            Searched.Rows = (from ServiceType in Filter
                             from VendorType in _context.tblVendorTypes
                             where VendorType.Active == true
                             && ServiceType.VendorTypeID == VendorType.VendorTypeID
                             orderby ServiceType.ServiceTypeCode
                             select new SetupServiceTypesGridRowBData()
                             {
                                 ServiceTypeID = ServiceType.ServiceTypeID,
                                 VendorTypeID = ServiceType.VendorTypeID,
                                 ServiceTypeCode = ServiceType.ServiceTypeCode,
                                 ServiceTypeDescription = ServiceType.ServiceTypeDesc,
                                 Per = ServiceType.Per,
                                 Active = ServiceType.Active,
                             }).Take(Data.PageNum * SetupServiceTypesGridBData.PageCount).ToList();
            Searched.VendorTypeID = (from VendorType in _context.tblVendorTypes
                                     where VendorType.Active == true
                                     select new
                                     {
                                         Key = VendorType.VendorTypeID,
                                         Value = VendorType.VendorTypeCode,
                                     }).ToDictionary(o => o.Key, o => o.Value);
            Searched.Per = new Dictionary<int, string>
            {
                {1,"Per"},
                {2,"Quantity"},
            };
            Searched.Active = new Dictionary<bool, string>
            {
                        {true,"Yes"},
                        {false,"No"},
            };
            return Searched;
        }
        public ErrorBInfo SaveServiceTypeDetailsData(SetupServiceTypesGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> ServiceTypeIDs = Data.Rows.Select(ServiceType => ServiceType.ServiceTypeID).ToList();
                var SaveEntityServiceTypes = _context.tblServiceTypes.Where(ServiceType => ServiceTypeIDs.Contains(ServiceType.ServiceTypeID)).ToList();
                foreach (var ServiceType in SaveEntityServiceTypes)
                {
                    var SaveData = Data.Rows.Where(data => data.ServiceTypeID == ServiceType.ServiceTypeID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        ServiceType.VendorTypeID = SaveData.VendorTypeID;
                        ServiceType.Per = SaveData.Per;
                        ServiceType.ServiceTypeCode = SaveData.ServiceTypeCode;
                        ServiceType.ServiceTypeDesc = SaveData.ServiceTypeDescription;
                        ServiceType.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region BankRepositrory
        public ErrorBInfo CreateBank(SetupBankDetailsBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblBank NewBank = new tblBank()
                {
                    BankName = Data.BankName,
                    BankCode = Data.BankCode,
                    BranchName = Data.BranchName,
                    ABANo = Data.ABANo,
                    AccNo = Data.AccNo,
                    Address = Data.Address,
                    City = (from city in _context.tblCities where city.CityID == Data.CityID select city.City).SingleOrDefault(),
                    CityID = Data.CityID,
                    StateID = Data.SelectedStateID,
                    PostalCode = Data.PostalCode,
                    Phone800 = Data.Phone800,
                    PhoneNumber = Data.PhoneNumber,
                    SwiftCode = Data.SwiftCode,
                    FaxNumber = Data.FaxNumber,
                    CountryID = Data.SelectedCountryID,
                    Note = Data.Notes,
                    Active = Data.Active,
                };

                _context.tblBanks.Add(NewBank);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }

            return Error;
        }
        public SetupBanksGridBData LoadAllBanks(int PageNumber)
        {
            SetupBanksGridBData Data = new SetupBanksGridBData();
            Data.Rows = new List<SetupBanksGridRowBData>();
            Data.Rows = (from Bank in _context.tblBanks
                         orderby Bank.BankCode
                         select new SetupBanksGridRowBData()
                         {
                             BankID = Bank.BankID,
                             BankName = Bank.BankName,
                             City = Bank.City,
                             BankCode = Bank.BankCode,
                             Active = Bank.Active,
                         }).Take(PageNumber * SetupBanksGridBData.PageCount).ToList();
            Data.City = (from city in _context.tblCities
                         where city.Active == true
                         orderby city.City
                         select new
                         {
                             Key = city.CityID,
                             Value = city.City
                         }).ToDictionary(o => o.Key, o => o.Value);
            Data.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return Data;
        }
        public ErrorBInfo DeleteBankById(int BankID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var Bank = _context.tblBanks.Where(bnk => bnk.BankID == BankID).SingleOrDefault();
            try
            {
                _context.tblBanks.Remove(Bank);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This Bank already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupBanksGridBData LoadSearchedBanks(SetupBanksGridBData Data)
        {
            var Filter = _context.tblBanks.AsQueryable();
            if (Data.BankCode != null && Data.BankCode.Length > 0)
                Filter = Filter.Where(Bank => Bank.BankCode.Contains(Data.BankCode.Trim()));
            if (Data.SelectedCity != null)
                Filter = Filter.Where(Bank => Bank.City == Data.SelectedCity);
            if (Data.BankName != null && Data.BankName.Length > 0)
                Filter = Filter.Where(Bank => Bank.BankName.Contains(Data.BankName.Trim()));
            if (Data.SelectedActive != null)
                Filter = Filter.Where(Bank => Bank.Active == Data.SelectedActive);
            SetupBanksGridBData searched;
            searched = new SetupBanksGridBData();
            searched.Rows = new List<SetupBanksGridRowBData>();
            searched.Rows = (from Bank in Filter
                             orderby Bank.BankCode
                             select new SetupBanksGridRowBData()
                             {
                                 BankID = Bank.BankID,
                                 BankName = Bank.BankName,
                                 City = Bank.City,
                                 BankCode = Bank.BankCode,
                                 Active = Bank.Active,
                             }).Take(Data.PageNum * SetupBanksGridBData.PageCount).ToList();
            searched.City = (from city in _context.tblCities
                             where city.Active == true
                             orderby city.City
                             select new
                             {
                                 Key = city.CityID,
                                 Value = city.City
                             }).ToDictionary(o => o.Key, o => o.Value);
            searched.Active = new Dictionary<bool, string>
            {
                  {true,"Yes"},
                  {false,"No"},
            };
            return searched;
        }
        public SetupBankDetailsBData LoadBankDetailsByID(int BankID)
        {
            SetupBankDetailsBData Data = null;
            if (BankID == 0)
            {
                Data = new SetupBankDetailsBData();
                Data.StateID = new Dictionary<string, string>();
                Data.City = new Dictionary<int, string>();
            }
            else
            {
                Data = (from Bank in _context.tblBanks
                        where Bank.BankID == BankID
                        select new SetupBankDetailsBData
                        {
                            BankID = Bank.BankID,
                            BankName = Bank.BankName,
                            BranchName = Bank.BranchName,
                            SwiftCode = Bank.SwiftCode,
                            BankCode = Bank.BankCode,
                            AccNo = Bank.AccNo,
                            ABANo = Bank.ABANo,
                            Address = Bank.Address,
                            SelectedCity = Bank.City,
                            SelectedStateID = Bank.StateID,
                            PostalCode = Bank.PostalCode,
                            Phone800 = Bank.Phone800,
                            PhoneNumber = Bank.PhoneNumber,
                            FaxNumber = Bank.FaxNumber,
                            SelectedCountryID = Bank.CountryID,
                            Notes = Bank.Note,
                            Active = Bank.Active,
                        }).SingleOrDefault();
                if (Data.SelectedCountryID == 1)
                {
                    Data.StateID = (from state in _context.tblStates where state.Active == true select new { Key = state.StateID, Value = state.StateID }).ToDictionary(o => o.Key, o => o.Value);
                    if (Data.SelectedStateID == null)
                    {
                        Data.City = new Dictionary<int, string>();
                    }
                    else
                    {
                        Data.City = (from city in _context.tblCities where city.StateID == Data.SelectedStateID && city.Active == true orderby city.City select new { Key = city.CityID, Value = city.City }).ToDictionary(o => o.Key, o => o.Value);
                        if (Data.City == null)
                            Data.City = new Dictionary<int, string>();
                        if (Data.CityID == null && Data.SelectedCity != null && Data.SelectedCity != "")
                        {
                            Data.City.Add(-1, Data.SelectedCity);
                            Data.CityID = -1;
                        }
                    }
                }
                else
                {
                    Data.StateID = new Dictionary<string, string>();
                    if (Data.SelectedCountryID == null)
                        Data.City = new Dictionary<int, string>();
                    else
                    {
                        Data.City = (from city in _context.tblCities where city.CountryID == Data.SelectedCountryID && city.Active == true orderby city.City select new { Key = city.CityID, Value = city.City }).ToDictionary(o => o.Key, o => o.Value);
                        if (Data.City == null)
                            Data.City = new Dictionary<int, string>();
                        if (Data.CityID == null && Data.SelectedCity != null && Data.SelectedCity != "")
                        {
                            Data.City.Add(-1, Data.SelectedCity);
                            Data.CityID = -1;
                        }
                    }
                }
            }
            Data.CountryID = (from cnt in _context.tblCountries where cnt.Active == true orderby cnt.CountryName select new { Key = cnt.CountryID, Value = cnt.CountryName }).ToDictionary(o => o.Key, o => o.Value);
            return Data;
        }
        public ErrorBInfo SaveBankDetailsData(SetupBankDetailsBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                var Edit = (from Bank in _context.tblBanks where Bank.BankID == Data.BankID select Bank).SingleOrDefault();
                if (Edit != null)
                {
                    Edit.ABANo = Data.ABANo;
                    Edit.AccNo = Data.AccNo;
                    Edit.BranchName = Data.BranchName;
                    Edit.BranchName = Data.BankName;
                    Edit.BankCode = Data.BankCode;
                    Edit.SwiftCode = Data.SwiftCode;
                    Edit.Address = Data.Address;
                    Edit.City = Data.CityID == -1 ? Data.SelectedCity : (from city in _context.tblCities where city.CityID == Data.CityID select city.City).SingleOrDefault();
                    Edit.CityID = Data.CityID != -1 ? Data.CityID : null;
                    Edit.StateID = Data.SelectedStateID;
                    Edit.PostalCode = Data.PostalCode;
                    Edit.Phone800 = Data.Phone800;
                    Edit.PhoneNumber = Data.PhoneNumber;
                    Edit.FaxNumber = Data.FaxNumber;
                    Edit.CountryID = Data.SelectedCountryID;
                    Edit.Note = Data.Notes;
                    Edit.Active = Data.Active;
                    _context.SaveChanges();
                }
                else
                {
                    Error.errorCode = 130;
                    Error.Message = "Bank Can`t be found:";
                }
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region CompanyRepositrory
        public ErrorBInfo CreateCompany(SetupCompanyDetailsBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblCompany NewCompany = new tblCompany()
                {
                    CompanyName = Data.CompanyName,
                    CompanyCode = Data.CompanyCode,
                    FedID = Data.FedID,
                    InvComm = Data.InvComm,
                    ProComm = Data.ProComm,
                    Address = Data.Address,
                    City = (from city in _context.tblCities where city.CityID == Data.CityID select city.City).SingleOrDefault(),
                    CityID = Data.CityID,
                    StateID = Data.SelectedStateID,
                    PostalCode = Data.PostalCode,
                    Phone800 = Data.Phone800,
                    PhoneNumber = Data.PhoneNumber,
                    FaxNumber = Data.FaxNumber,
                    Brochure_Htl = Data.Brochure_Htl,
                    Brochure_Rst = Data.Brochure_Rst,
                    Brochure_Oth = Data.Brochure_Oth,
                    Contract_Htl = Data.Contract_Htl,
                    Contract_Rst = Data.Contract_Rst,
                    Contract_Oth = Data.Contract_Oth,
                    Resume_Gui = Data.Resume_Gui,
                    Resume_Emp = Data.Resume_Emp,
                    ProposalDir = Data.ProposalDir,
                    Note = Data.Notes,
                    Active = Data.Active,
                };

                _context.tblCompanies.Add(NewCompany);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }

            return Error;
        }
        public SetupCompaniesGridBData LoadAllCompanies(int PageNumber)
        {
            SetupCompaniesGridBData Data = new SetupCompaniesGridBData();
            Data.Rows = new List<SetupCompaniesGridRowBData>();
            Data.Rows = (from Company in _context.tblCompanies
                         orderby Company.CompanyCode
                         select new SetupCompaniesGridRowBData()
                         {
                             CompanyID = Company.CompanyID,
                             CompanyName = Company.CompanyName,
                             City = Company.City,
                             CompanyCode = Company.CompanyCode,
                             Active = Company.Active,
                         }).Take(PageNumber * SetupCompaniesGridBData.PageCount).ToList();
            Data.City = (from city in _context.tblCities
                         where city.CountryID == 1
                         && city.Active == true
                         orderby city.City
                         select new
                         {
                             Key = city.CityID,
                             Value = city.City
                         }).ToDictionary(o => o.Key, o => o.Value);
            Data.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return Data;
        }
        public ErrorBInfo DeleteCompanyById(int CompanyID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var Company = _context.tblCompanies.Where(company => company.CompanyID == CompanyID).SingleOrDefault();
            try
            {
                _context.tblCompanies.Remove(Company);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This Company already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupCompaniesGridBData LoadSearchedCompanies(SetupCompaniesGridBData Data)
        {
            var Filter = _context.tblCompanies.AsQueryable();
            if (Data.CompanyCode != null && Data.CompanyCode.Length > 0)
                Filter = Filter.Where(Company => Company.CompanyCode.Contains(Data.CompanyCode.Trim()));
            if (Data.SelectedCity != null)
                Filter = Filter.Where(Company => Company.City == Data.SelectedCity);
            if (Data.CompanyName != null && Data.CompanyName.Length > 0)
                Filter = Filter.Where(Company => Company.CompanyName.Contains(Data.CompanyName.Trim()));
            if (Data.SelectedActive != null)
                Filter = Filter.Where(Company => Company.Active == Data.SelectedActive);
            SetupCompaniesGridBData searched;
            searched = new SetupCompaniesGridBData();
            searched.Rows = new List<SetupCompaniesGridRowBData>();
            searched.Rows = (from Company in Filter
                             orderby Company.CompanyCode
                             select new SetupCompaniesGridRowBData()
                             {
                                 CompanyID = Company.CompanyID,
                                 CompanyName = Company.CompanyName,
                                 City = Company.City,
                                 CompanyCode = Company.CompanyCode,
                                 Active = Company.Active,
                             }).Take(Data.PageNum * SetupCompaniesGridBData.PageCount).ToList();
            searched.City = (from city in _context.tblCities
                             where city.CountryID == 1
                             && city.Active == true
                             orderby city.City
                             select new
                             {
                                 Key = city.CityID,
                                 Value = city.City
                             }).ToDictionary(o => o.Key, o => o.Value);
            searched.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return searched;
        }
        public SetupCompanyDetailsBData LoadCompanyDetailsByID(int CompanyID)
        {
            SetupCompanyDetailsBData Data = null;
            if (CompanyID == 0)
            {
                Data = new SetupCompanyDetailsBData();
                Data.City = new Dictionary<int, string>();
            }
            else
            {
                Data = (from Company in _context.tblCompanies
                        where Company.CompanyID == CompanyID
                        select new SetupCompanyDetailsBData
                        {
                            CompanyID = Company.CompanyID,
                            CompanyName = Company.CompanyName,
                            CompanyCode = Company.CompanyCode,
                            Address = Company.Address,
                            SelectedCity = Company.City,
                            SelectedStateID = Company.StateID,
                            PostalCode = Company.PostalCode,
                            Phone800 = Company.Phone800,
                            PhoneNumber = Company.PhoneNumber,
                            FaxNumber = Company.FaxNumber,
                            Notes = Company.Note,
                            FedID = Company.FedID,
                            InvComm = Company.InvComm,
                            ProComm = Company.ProComm,
                            Brochure_Htl = Company.Brochure_Htl,
                            Brochure_Rst = Company.Brochure_Rst,
                            Brochure_Oth = Company.Brochure_Oth,
                            Contract_Htl = Company.Contract_Htl,
                            Contract_Rst = Company.Contract_Rst,
                            Contract_Oth = Company.Contract_Oth,
                            Resume_Gui = Company.Resume_Gui,
                            Resume_Emp = Company.Resume_Emp,
                            ProposalDir = Company.ProposalDir,
                            Active = Company.Active,
                        }).SingleOrDefault();
                if (Data.SelectedStateID == null)
                {
                    Data.City = new Dictionary<int, string>();
                }
                else
                {
                    Data.City = (from city in _context.tblCities where city.StateID == Data.SelectedStateID && city.Active == true orderby city.City select new { Key = city.CityID, Value = city.City }).ToDictionary(o => o.Key, o => o.Value);
                    if (Data.City == null)
                        Data.City = new Dictionary<int, string>();
                    if (Data.CityID == null && Data.SelectedCity != null && Data.SelectedCity != "")
                    {
                        Data.City.Add(-1, Data.SelectedCity);
                        Data.CityID = -1;
                    }
                }
            }
            Data.StateID = (from state in _context.tblStates where state.Active == true select new { Key = state.StateID, Value = state.StateID }).ToDictionary(o => o.Key, o => o.Value);
            return Data;
        }
        public ErrorBInfo SaveCompanyDetailsData(SetupCompanyDetailsBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                var Edit = (from Company in _context.tblCompanies where Company.CompanyID == Data.CompanyID select Company).SingleOrDefault();
                if (Edit != null)
                {
                    Edit.CompanyName = Data.CompanyName;
                    Edit.CompanyCode = Data.CompanyCode;
                    Edit.Address = Data.Address;
                    Edit.City = Data.CityID == -1 ? Data.SelectedCity : (from city in _context.tblCities where city.CityID == Data.CityID select city.City).SingleOrDefault();
                    Edit.CityID = Data.CityID != -1 ? Data.CityID : null;
                    Edit.StateID = Data.SelectedStateID;
                    Edit.PostalCode = Data.PostalCode;
                    Edit.Phone800 = Data.Phone800;
                    Edit.PhoneNumber = Data.PhoneNumber;
                    Edit.FaxNumber = Data.FaxNumber;
                    Edit.Note = Data.Notes;
                    Edit.FedID = Data.FedID;
                    Edit.InvComm = Data.InvComm;
                    Edit.ProComm = Data.ProComm;
                    Edit.Brochure_Htl = Data.Brochure_Htl;
                    Edit.Brochure_Rst = Data.Brochure_Rst;
                    Edit.Brochure_Oth = Data.Brochure_Oth;
                    Edit.Contract_Htl = Data.Contract_Htl;
                    Edit.Contract_Rst = Data.Contract_Rst;
                    Edit.Contract_Oth = Data.Contract_Oth;
                    Edit.Resume_Gui = Data.Resume_Gui;
                    Edit.Resume_Emp = Data.Resume_Emp;
                    Edit.ProposalDir = Data.ProposalDir;
                    Edit.Active = Data.Active;
                    _context.SaveChanges();
                }
                else
                {
                    Error.errorCode = 130;
                    Error.Message = "Company Can`t be found:";
                }
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region DetailRepository
        public ErrorBInfo CreateDetail(SetupDetailsGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblDetail NewDetail = new tblDetail()
                {
                    DetailTypeID = Data.Rows[0].DetailTypeID,
                    DetailCode = Data.Rows[0].DetailCode,
                    DetailDescription = Data.Rows[0].DetailDescription,
                    ServiceTypeID = Data.Rows[0].DetailServiceTypeID,
                    TimeFlg = Data.Rows[0].TimeFlg,
                    VendFlg = Data.Rows[0].VendFlg,
                    AddrFlg = Data.Rows[0].AddrFlg,
                    GuidFlg = Data.Rows[0].GuidFlg,
                    Active = Data.Rows[0].Active,
                };
                _context.tblDetails.Add(NewDetail);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupDetailsGridBData LoadDetails(int PageNumber)
        {
            SetupDetailsGridBData Details = new SetupDetailsGridBData();
            if (PageNumber == -1)
            {
                Details.DetailTypeID = (from DetailType in _context.tblDetailTypes
                                        from VendorType in _context.tblVendorTypes
                                        where DetailType.VendorTypeID == VendorType.VendorTypeID
                                        && DetailType.Active == true
                                        && VendorType.Active == true
                                        select new
                                        {
                                            Key = DetailType.DetailTypeID,
                                            Value =
                                            (DetailType.DetailTypeDesc != null ? DetailType.DetailTypeDesc : "")
                                            + " " +
                                            (VendorType.VendorTypeCode != null ? VendorType.VendorTypeCode : ""),
                                        }).ToDictionary(o => o.Key, o => o.Value);
                Details.Rows = new List<SetupDetailsGridRowBData>();
                if (Details.DetailTypeID != null)
                {
                    Details.Rows.Add(new SetupDetailsGridRowBData
                    {
                        DetailServiceTypeID = null,
                    });
                    int DetailTypeFirstKey = Details.DetailTypeID.First().Key;
                    foreach (var Data in Details.Rows)
                    {
                        Data.ServiceTypeID = (from ServiceType in _context.tblServiceTypes
                                              from DetailsType in _context.tblDetailTypes
                                              where ServiceType.VendorTypeID == DetailsType.VendorTypeID
                                              && DetailTypeFirstKey == DetailsType.DetailTypeID
                                              && ServiceType.Active == true
                                              && DetailsType.Active == true
                                              select new
                                              {
                                                  Key = ServiceType.ServiceTypeID,
                                                  Value =
                                                (ServiceType.ServiceTypeCode != null ? ServiceType.ServiceTypeCode : "")
                                                + " " +
                                                (ServiceType.ServiceTypeDesc != null ? ServiceType.ServiceTypeDesc : ""),
                                              }).ToDictionary(o => o.Key, o => o.Value);
                    }

                }
                return Details;
            }
            Details.Rows = new List<SetupDetailsGridRowBData>();
            Details.Rows = (from Detail in _context.tblDetails
                            orderby Detail.DetailCode
                            select new SetupDetailsGridRowBData()
                                 {
                                     DetailID = Detail.DetailID,
                                     DetailTypeID = Detail.DetailTypeID,
                                     DetailCode = Detail.DetailCode,
                                     DetailDescription = Detail.DetailDescription,
                                     TimeFlg = Detail.TimeFlg,
                                     VendFlg = Detail.VendFlg,
                                     AddrFlg = Detail.AddrFlg,
                                     GuidFlg = Detail.GuidFlg,
                                     DetailServiceTypeID = Detail.ServiceTypeID,
                                     Active = Detail.Active,
                                 }).Take(PageNumber * SetupDetailsGridBData.PageCount).ToList();
            foreach (var Data in Details.Rows)
            {
                Data.ServiceTypeID = (from ServiceType in _context.tblServiceTypes
                                      from DetailsType in _context.tblDetailTypes
                                      where ServiceType.VendorTypeID == DetailsType.VendorTypeID
                                      && Data.DetailTypeID == DetailsType.DetailTypeID
                                      && ServiceType.Active == true
                                      && DetailsType.Active == true
                                      select new
                                      {
                                          Key = ServiceType.ServiceTypeID,
                                          Value =
                                        (ServiceType.ServiceTypeCode != null ? ServiceType.ServiceTypeCode : "")
                                        + " " +
                                        (ServiceType.ServiceTypeDesc != null ? ServiceType.ServiceTypeDesc : ""),
                                      }).ToDictionary(o => o.Key, o => o.Value);
            }
            Details.DetailTypeID = (from DetailType in _context.tblDetailTypes
                                    from VendorType in _context.tblVendorTypes
                                    where DetailType.VendorTypeID == VendorType.VendorTypeID
                                    && DetailType.Active == true
                                    && VendorType.Active == true
                                    select new
                                    {
                                        Key = DetailType.DetailTypeID,
                                        Value =
                                        (DetailType.DetailTypeDesc != null ? DetailType.DetailTypeDesc : "")
                                        + " " +
                                        (VendorType.VendorTypeCode != null ? VendorType.VendorTypeCode : ""),
                                    }).ToDictionary(o => o.Key, o => o.Value);
            Details.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return Details;
        }
        public SetupDetailsGridBData LoadSearchedDetails(SetupDetailsGridBData Data)
        {
            var Filter = _context.tblDetails.AsQueryable();
            if (Data.DetailDetailTypeID != null && Data.DetailDetailTypeID != 0)
                Filter = Filter.Where(Detail => Detail.DetailTypeID == Data.DetailDetailTypeID);
            if (Data.DetailDescription != null && Data.DetailDescription.Length > 0)
                Filter = Filter.Where(Detail => Detail.DetailDescription.Contains(Data.DetailDescription.Trim()));
            if (Data.DetailCode != null && Data.DetailCode.Length > 0)
                Filter = Filter.Where(Detail => Detail.DetailCode.Contains(Data.DetailCode.Trim()));
            if (Data.SelectedActive != null)
                Filter = Filter.Where(Detail => Detail.Active == Data.SelectedActive);
            SetupDetailsGridBData Searched;
            Searched = new SetupDetailsGridBData();
            Searched.Rows = new List<SetupDetailsGridRowBData>();
            Searched.Rows = (from Detail in Filter
                             orderby Detail.DetailCode
                             select new SetupDetailsGridRowBData()
                             {
                                 DetailID = Detail.DetailID,
                                 DetailCode = Detail.DetailCode,
                                 DetailDescription = Detail.DetailDescription,
                                 DetailTypeID = Detail.DetailTypeID,
                                 AddrFlg = Detail.AddrFlg,
                                 GuidFlg = Detail.GuidFlg,
                                 VendFlg = Detail.VendFlg,
                                 TimeFlg = Detail.TimeFlg,
                                 DetailServiceTypeID = Detail.ServiceTypeID,
                                 Active = Detail.Active,
                             }).Take(Data.PageNum * SetupDetailsGridBData.PageCount).ToList();
            Searched.DetailTypeID = (from VendorType in _context.tblVendorTypes
                                     where VendorType.Active == true
                                     select new
                                     {
                                         Key = VendorType.VendorTypeID,
                                         Value = VendorType.VendorTypeCode,
                                     }).ToDictionary(o => o.Key, o => o.Value);
            foreach (var search in Searched.Rows)
            {
                search.ServiceTypeID = (from ServiceType in _context.tblServiceTypes
                                        from DetailsType in _context.tblDetailTypes
                                        where ServiceType.VendorTypeID == DetailsType.VendorTypeID
                                        && search.DetailTypeID == DetailsType.DetailTypeID
                                        && ServiceType.Active == true
                                        && DetailsType.Active == true
                                        select new
                                        {
                                            Key = ServiceType.ServiceTypeID,
                                            Value =
                                          (ServiceType.ServiceTypeCode != null ? ServiceType.ServiceTypeCode : "")
                                          + " " +
                                          (ServiceType.ServiceTypeDesc != null ? ServiceType.ServiceTypeDesc : ""),
                                        }).ToDictionary(o => o.Key, o => o.Value);
            }
            Searched.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return Searched;
        }
        public ErrorBInfo DeleteDetailById(int DetailID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var Detail = _context.tblDetails.Where(Det => Det.DetailID == DetailID).SingleOrDefault();
            try
            {
                _context.tblDetails.Remove(Detail);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This Detail already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public Dictionary<int, string> GetServiceTypes(int DetailTypeID)
        {
            Dictionary<int, string> ServiceTypes = new Dictionary<int, string>();
            ServiceTypes = (from ServiceType in _context.tblServiceTypes
                            from DetailsType in _context.tblDetailTypes
                            where ServiceType.VendorTypeID == DetailsType.VendorTypeID
                            && DetailTypeID == DetailsType.DetailTypeID
                            && ServiceType.Active == true
                            && DetailsType.Active == true
                            select new
                            {
                                Key = ServiceType.ServiceTypeID,
                                Value =
                              (ServiceType.ServiceTypeCode != null ? ServiceType.ServiceTypeCode : "")
                              + " " +
                              (ServiceType.ServiceTypeDesc != null ? ServiceType.ServiceTypeDesc : ""),
                            }).ToDictionary(o => o.Key, o => o.Value);
            return ServiceTypes;
        }
        public ErrorBInfo SaveDetailDetailsData(SetupDetailsGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> DetailIDs = Data.Rows.Select(Detail => Detail.DetailID).ToList();
                var SaveEntityDetails = _context.tblDetails.Where(Detail => DetailIDs.Contains(Detail.DetailID)).ToList();
                foreach (var Detail in SaveEntityDetails)
                {
                    var SaveData = Data.Rows.Where(data => data.DetailID == Detail.DetailID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        Detail.DetailTypeID = SaveData.DetailTypeID;
                        Detail.ServiceTypeID = SaveData.DetailServiceTypeID;
                        Detail.DetailCode = SaveData.DetailCode;
                        Detail.DetailDescription = SaveData.DetailDescription;
                        Detail.GuidFlg = SaveData.GuidFlg;
                        Detail.TimeFlg = SaveData.TimeFlg;
                        Detail.VendFlg = SaveData.VendFlg;
                        Detail.AddrFlg = SaveData.AddrFlg;
                        Detail.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region CityRepository
        public ErrorBInfo CreateCity(SetupCitiesGridRowBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblCity NewCity = new tblCity()
                {
                    City = Data.City,
                    CountryID = Data.SelectedCountry,
                    StateID = Data.SelectedState,
                    Active = Data.Active,
                };
                _context.tblCities.Add(NewCity);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupCitiesGridRowBData LoadAddCityDetails()
        {
            SetupCitiesGridRowBData Cities = new SetupCitiesGridRowBData();
            Cities.CountryID = new Dictionary<int, string>();
            Cities.CountryID = (from cnt in _context.tblCountries where cnt.Active == true orderby cnt.CountryName select new { Key = cnt.CountryID, Value = cnt.CountryName }).ToDictionary(o => o.Key, o => o.Value);
            if (Cities.CountryID == null)
                Cities.CountryID = new Dictionary<int, string>();
            Cities.State = new Dictionary<string, string>();
            Cities.State = (from state in _context.tblStates where state.Active == true select new { Key = state.StateID, Value = state.StateID }).ToDictionary(o => o.Key, o => o.Value);
            if (Cities.State == null)
                Cities.State = new Dictionary<string, string>();
            return Cities;
        }
        public SetupCitiesGridBData LoadCities(int PageNumber)
        {
            SetupCitiesGridBData Cities = new SetupCitiesGridBData();
            Cities.Rows = new List<SetupCitiesGridRowBData>();
            Cities.Rows = (from City in _context.tblCities
                           orderby City.City
                           select new SetupCitiesGridRowBData()
                            {
                                CityID = City.CityID,
                                SelectedCountry = City.CountryID,
                                SelectedState = City.StateID,
                                City = City.City,
                                Active = City.Active,
                            }).Take(PageNumber * SetupCitiesGridBData.PageCount).ToList();
            Cities.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            Cities.CountryID = new Dictionary<int, string>();
            Cities.CountryID = (from cnt in _context.tblCountries where cnt.Active == true orderby cnt.CountryName select new { Key = cnt.CountryID, Value = cnt.CountryName }).ToDictionary(o => o.Key, o => o.Value);
            if (Cities.CountryID == null)
                Cities.CountryID = new Dictionary<int, string>();
            Cities.State = new Dictionary<string, string>();
            Cities.State = (from state in _context.tblStates where state.Active == true select new { Key = state.StateID, Value = state.StateID }).ToDictionary(o => o.Key, o => o.Value);
            if (Cities.State == null)
                Cities.State = new Dictionary<string, string>();
            return Cities;
        }
        public ErrorBInfo DeleteCityById(int CityID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var City = _context.tblCities.Where(city => city.CityID == CityID).SingleOrDefault();
            try
            {
                _context.tblCities.Remove(City);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This City already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupCitiesGridBData LoadSearchedCities(SetupCitiesGridBData Data)
        {
            var Filter = _context.tblCities.AsQueryable();
            if (Data.City != null && Data.City.Length > 0)
                Filter = Filter.Where(city => city.City.Contains(Data.City.Trim()));
            if (Data.SelectedActive != null)
                Filter = Filter.Where(city => city.Active == Data.SelectedActive);
            if (Data.SelectedActive != null)
                Filter = Filter.Where(city => city.Active == Data.SelectedActive);
            if (Data.SelectedState != "" && Data.SelectedState != null)
                Filter = Filter.Where(city => city.StateID == Data.SelectedState);
            if (Data.SelectedCountry != 0)
                Filter = Filter.Where(city => city.CountryID == Data.SelectedCountry);
            SetupCitiesGridBData SearchedData;
            SearchedData = new SetupCitiesGridBData();
            SearchedData.Rows = new List<SetupCitiesGridRowBData>();
            SearchedData.Rows = (from City in Filter
                                 orderby City.City
                                 select new SetupCitiesGridRowBData()
                                 {
                                     CityID = City.CityID,
                                     City = City.City,
                                     Active = City.Active,
                                     SelectedCountry = City.CountryID,
                                     SelectedState = City.StateID,
                                 }).Take(Data.PageNum * SetupRegionsGridBData.PageCount).ToList();
            SearchedData.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            SearchedData.CountryID = (from cnt in _context.tblCountries where cnt.Active == true orderby cnt.CountryName select new { Key = cnt.CountryID, Value = cnt.CountryName }).ToDictionary(o => o.Key, o => o.Value);
            SearchedData.CountryID = new Dictionary<int, string>();
            SearchedData.CountryID = (from cnt in _context.tblCountries where cnt.Active == true orderby cnt.CountryName select new { Key = cnt.CountryID, Value = cnt.CountryName }).ToDictionary(o => o.Key, o => o.Value);
            if (SearchedData.CountryID == null)
                SearchedData.CountryID = new Dictionary<int, string>();
            SearchedData.State = new Dictionary<string, string>();
            SearchedData.State = (from state in _context.tblStates where state.Active == true orderby state.StateID select new { Key = state.StateID, Value = state.StateID }).ToDictionary(o => o.Key, o => o.Value);
            if (SearchedData.State == null)
                SearchedData.State = new Dictionary<string, string>();
            return SearchedData;
        }
        public ErrorBInfo SaveCityDetailsData(SetupCitiesGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> CityIDs = Data.Rows.Select(city => city.CityID).ToList();
                var SaveEntityCities = _context.tblCities.Where(city => CityIDs.Contains(city.CityID)).ToList();
                foreach (var City in SaveEntityCities)
                {
                    var SaveData = Data.Rows.Where(data => data.CityID == City.CityID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        City.City = SaveData.City;
                        City.CountryID = SaveData.SelectedCountry;
                        City.StateID = SaveData.SelectedState;
                        City.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region QuotaRegionRepository
        public ErrorBInfo CreateQuotaRegion(SetupQuotaRegionsGridRowBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblQuotaRegion NewQuotaRegion = new tblQuotaRegion()
                {
                    Region = Data.Region,
                    Active = Data.Active,
                };
                _context.tblQuotaRegions.Add(NewQuotaRegion);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupQuotaRegionsGridBData LoadQuotaRegions(int PageNumber)
        {
            SetupQuotaRegionsGridBData QuotaRegions = new SetupQuotaRegionsGridBData();
            QuotaRegions.Rows = new List<SetupQuotaRegionsGridRowBData>();
            QuotaRegions.Rows = (from QuotaRegion in _context.tblQuotaRegions
                                 orderby QuotaRegion.Region
                                 select new SetupQuotaRegionsGridRowBData()
                                 {
                                     RegionID = QuotaRegion.RegionID,
                                     Region = QuotaRegion.Region,
                                     Active = QuotaRegion.Active,
                                 }).Take(PageNumber * SetupQuotaRegionsGridBData.PageCount).ToList();
            QuotaRegions.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return QuotaRegions;
        }
        public ErrorBInfo DeleteQuotaRegionById(int RegionID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var Region = _context.tblQuotaRegions.Where(quotaRegion => quotaRegion.RegionID == RegionID).SingleOrDefault();
            try
            {
                _context.tblQuotaRegions.Remove(Region);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This Quota Region already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupQuotaRegionsGridBData LoadSearchedQuotaRegions(SetupQuotaRegionsGridBData Data)
        {
            var Filter = _context.tblQuotaRegions.AsQueryable();
            if (Data.Region != null && Data.Region.Length > 0)
                Filter = Filter.Where(Region => Region.Region.Contains(Data.Region.Trim()));
            if (Data.SelectedActive != null)
                Filter = Filter.Where(Region => Region.Active == Data.SelectedActive);
            SetupQuotaRegionsGridBData SearchedData;
            SearchedData = new SetupQuotaRegionsGridBData();
            SearchedData.Rows = new List<SetupQuotaRegionsGridRowBData>();
            SearchedData.Rows = (from QuotaRegion in Filter
                                 orderby QuotaRegion.Region
                                 select new SetupQuotaRegionsGridRowBData()
                                 {
                                     RegionID = QuotaRegion.RegionID,
                                     Region = QuotaRegion.Region,
                                     Active = QuotaRegion.Active,
                                 }).Take(Data.PageNum * SetupQuotaRegionsGridBData.PageCount).ToList();
            SearchedData.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return SearchedData;
        }
        public ErrorBInfo SaveQuotaRegionDetailsData(SetupQuotaRegionsGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> RegionIDs = Data.Rows.Select(Region => Region.RegionID).ToList();
                var SaveEntityQuotaRegions = _context.tblQuotaRegions.Where(Region => RegionIDs.Contains(Region.RegionID)).ToList();
                foreach (var QuotaRegion in SaveEntityQuotaRegions)
                {
                    var SaveData = Data.Rows.Where(data => data.RegionID == QuotaRegion.RegionID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        QuotaRegion.Region = SaveData.Region;
                        QuotaRegion.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region CardRepository
        public ErrorBInfo CreateCard(SetupCardsGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblCard NewCard = new tblCard()
                {
                    CardTypeID = Data.Rows[0].CardTypeID,
                    CardNumber = Data.Rows[0].CardNumber,
                    PlaceHolderName = Data.Rows[0].PlaceHolderName,
                    CardValidFromDate = Data.Rows[0].CardValidFromDate,
                    CardValidToDate = Data.Rows[0].CardValidToDate,
                    Active = Data.Rows[0].Active,
                };
                _context.tblCards.Add(NewCard);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupCardsGridBData LoadCards(int PageNumber)
        {
            SetupCardsGridBData Cards = new SetupCardsGridBData();
            if (PageNumber == -1)
            {
                Cards.CardType = (from cardtype in _context.tblCardTypes
                                  select new
                                  {
                                      Key = cardtype.CardTypeID,
                                      Value = cardtype.CardTypeCode,
                                  }).ToDictionary(o => o.Key, o => o.Value);
                return Cards;
            }
            Cards.Rows = new List<SetupCardsGridRowBData>();
            Cards.Rows = (from card in _context.tblCards
                          orderby card.PlaceHolderName
                          select new SetupCardsGridRowBData()
                          {
                              CardID = card.CardID,
                              CardTypeID = card.CardTypeID,
                              PlaceHolderName = card.PlaceHolderName,
                              CardNumber = card.CardNumber,
                              CardValidFromDate = card.CardValidFromDate,
                              CardValidToDate = card.CardValidToDate,
                              Active = card.Active,
                          }).Take(PageNumber * SetupAreasGridBData.PageCount).ToList();
            Cards.CardType = (from cardtype in _context.tblCardTypes
                              select new
                              {
                                  Key = cardtype.CardTypeID,
                                  Value = cardtype.CardTypeCode,
                              }).ToDictionary(o => o.Key, o => o.Value);
            Cards.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return Cards;
        }
        public ErrorBInfo DeleteCardById(int CardID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var Card = _context.tblCards.Where(card => card.CardID == CardID).SingleOrDefault();
            try
            {
                _context.tblCards.Remove(Card);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This Card already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupCardsGridBData LoadSearchedCards(SetupCardsGridBData Data)
        {
            var Filter = _context.tblCards.AsQueryable();
            if (Data.CardTypeID != 0)
                Filter = Filter.Where(card => card.CardTypeID == Data.CardTypeID);
            if (Data.CardNumber != null && Data.CardNumber.Length > 0)
                Filter = Filter.Where(card => card.CardNumber.Contains(Data.CardNumber.Trim()));
            if (Data.PlaceHolderName != null && Data.PlaceHolderName.Length > 0)
                Filter = Filter.Where(card => card.PlaceHolderName.Contains(Data.PlaceHolderName.Trim()));
            if (Data.SelectedActive != null)
                Filter = Filter.Where(card => card.Active == Data.SelectedActive);
            SetupCardsGridBData Searched;
            Searched = new SetupCardsGridBData();
            Searched.Rows = new List<SetupCardsGridRowBData>();
            Searched.Rows = (from Card in Filter
                             orderby Card.PlaceHolderName
                             select new SetupCardsGridRowBData()
                             {
                                 CardID = Card.CardID,
                                 CardTypeID = Card.CardTypeID,
                                 CardNumber = Card.CardNumber,
                                 PlaceHolderName = Card.PlaceHolderName,
                                 CardValidFromDate = Card.CardValidFromDate,
                                 CardValidToDate = Card.CardValidToDate,
                                 Active = Card.Active,
                             }).Take(Data.PageNum * SetupCardsGridBData.PageCount).ToList();
            Searched.CardType = (from cardtype in _context.tblCardTypes
                                 select new
                                 {
                                     Key = cardtype.CardTypeID,
                                     Value = cardtype.CardTypeCode,
                                 }).ToDictionary(o => o.Key, o => o.Value);
            Searched.Active = new Dictionary<bool, string>
                 {
                             {true,"Yes"},
                             {false,"No"},
                 };
            return Searched;
        }
        public ErrorBInfo SaveCardDetailsData(SetupCardsGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> CardIDs = Data.Rows.Select(card => card.CardID).ToList();
                var SaveEntityCards = _context.tblCards.Where(card => CardIDs.Contains(card.CardID)).ToList();
                foreach (var card in SaveEntityCards)
                {
                    var SaveData = Data.Rows.Where(data => data.CardID == card.CardID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        card.CardTypeID = SaveData.CardTypeID;
                        card.CardNumber = SaveData.CardNumber;
                        card.PlaceHolderName = SaveData.PlaceHolderName;
                        card.CardValidFromDate = SaveData.CardValidFromDate;
                        card.CardValidToDate = SaveData.CardValidToDate;
                        card.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region TechTypeRepository
        public ErrorBInfo CreateTechType(SetupTechTypesGridRowBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblTechDetailType NewTechType = new tblTechDetailType()
                {
                    TechTypeCode = Data.TechTypeCode,
                    TechTypeDesc = Data.TechTypeDescription,
                    Active = Data.Active,
                };
                _context.tblTechDetailTypes.Add(NewTechType);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupTechTypesGridBData LoadTechTypes(int PageNumber)
        {
            SetupTechTypesGridBData TechTypes = new SetupTechTypesGridBData();
            TechTypes.Rows = new List<SetupTechTypesGridRowBData>();
            TechTypes.Rows = (from TechType in _context.tblTechDetailTypes
                              orderby TechType.TechTypeCode
                              select new SetupTechTypesGridRowBData()
                              {
                                  TechTypeID = TechType.TechTypeID,
                                  TechTypeCode = TechType.TechTypeCode,
                                  TechTypeDescription = TechType.TechTypeDesc,
                                  Active = TechType.Active,
                              }).Take(PageNumber * SetupTechTypesGridBData.PageCount).ToList();
            TechTypes.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return TechTypes;
        }
        public ErrorBInfo DeleteTechTypeById(int TechTypeID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var TechType = _context.tblTechDetailTypes.Where(TType => TType.TechTypeID == TechTypeID).SingleOrDefault();
            try
            {
                _context.tblTechDetailTypes.Remove(TechType);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This TechType already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupTechTypesGridBData LoadSearchedTechTypes(SetupTechTypesGridBData Data)
        {
            var FilterTechType = _context.tblTechDetailTypes.AsQueryable();
            if (Data.TechTypeCode != null && Data.TechTypeCode.Length > 0)
                FilterTechType = FilterTechType.Where(TechType => TechType.TechTypeCode.Contains(Data.TechTypeCode.Trim()));
            if (Data.TechTypeDescription != null && Data.TechTypeDescription.Length > 0)
                FilterTechType = FilterTechType.Where(TechType => TechType.TechTypeDesc.Contains(Data.TechTypeDescription.Trim()));
            if (Data.SelectedActive != null)
                FilterTechType = FilterTechType.Where(TechType => TechType.Active == Data.SelectedActive);
            SetupTechTypesGridBData SearchedTechTypes;
            SearchedTechTypes = new SetupTechTypesGridBData();
            SearchedTechTypes.Rows = new List<SetupTechTypesGridRowBData>();
            SearchedTechTypes.Rows = (from TechType in FilterTechType
                                      orderby TechType.TechTypeCode
                                      select new SetupTechTypesGridRowBData()
                                    {
                                        TechTypeID = TechType.TechTypeID,
                                        TechTypeCode = TechType.TechTypeCode,
                                        TechTypeDescription = TechType.TechTypeDesc,
                                        Active = TechType.Active,
                                    }).Take(Data.PageNum * SetupRegionsGridBData.PageCount).ToList();
            SearchedTechTypes.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return SearchedTechTypes;
        }
        public ErrorBInfo SaveTechTypeDetailsData(SetupTechTypesGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> TechTypeIDs = Data.Rows.Select(TechType => TechType.TechTypeID).ToList();
                var SaveEntityTechTypes = _context.tblTechDetailTypes.Where(TechType => TechTypeIDs.Contains(TechType.TechTypeID)).ToList();
                foreach (var TechType in SaveEntityTechTypes)
                {
                    var SaveData = Data.Rows.Where(data => data.TechTypeID == TechType.TechTypeID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        TechType.TechTypeCode = SaveData.TechTypeCode;
                        TechType.TechTypeDesc = SaveData.TechTypeDescription;
                        TechType.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region VConfStatusRepository
        public ErrorBInfo CreateVConfStatus(SetupVConfStatusGridRowBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblVConfirmStatu NewVConfStatus = new tblVConfirmStatu()
                {
                    StatusDesc = Data.StatusDesc,
                    StatusOrder = Data.StatusOrder,
                    Active = Data.Active,
                };
                _context.tblVConfirmStatus.Add(NewVConfStatus);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupVConfStatusGridBData LoadVConfStatus(int PageNumber)
        {
            SetupVConfStatusGridBData VConfStatus = new SetupVConfStatusGridBData();
            VConfStatus.Rows = new List<SetupVConfStatusGridRowBData>();
            VConfStatus.Rows = (from VConfSt in _context.tblVConfirmStatus
                                orderby VConfSt.StatusOrder
                                select new SetupVConfStatusGridRowBData()
                                {
                                    StatusID = VConfSt.StatusID,
                                    StatusOrder = VConfSt.StatusOrder,
                                    StatusDesc = VConfSt.StatusDesc,
                                    Active = VConfSt.Active,
                                }).Take(PageNumber * SetupVConfStatusGridBData.PageCount).ToList();
            VConfStatus.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return VConfStatus;
        }
        public ErrorBInfo DeleteVConfStatusById(int StatusID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var VConfStatus = _context.tblVConfirmStatus.Where(VCS => VCS.StatusID == StatusID).SingleOrDefault();
            try
            {
                _context.tblVConfirmStatus.Remove(VConfStatus);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This Vendor Confirm Status already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupVConfStatusGridBData LoadSearchedVConfStatus(SetupVConfStatusGridBData Data)
        {
            var FilterVConfStatus = _context.tblVConfirmStatus.AsQueryable();
            if (Data.StatusDesc != null && Data.StatusDesc.Length > 0)
                FilterVConfStatus = FilterVConfStatus.Where(VCS => VCS.StatusDesc.Contains(Data.StatusDesc.Trim()));
            if (Data.SelectedActive != null)
                FilterVConfStatus = FilterVConfStatus.Where(VCS => VCS.Active == Data.SelectedActive);
            SetupVConfStatusGridBData SearchedVConfStatus;
            SearchedVConfStatus = new SetupVConfStatusGridBData();
            SearchedVConfStatus.Rows = new List<SetupVConfStatusGridRowBData>();
            SearchedVConfStatus.Rows = (from VCStatus in FilterVConfStatus
                                        orderby VCStatus.StatusOrder
                                        select new SetupVConfStatusGridRowBData()
                                        {
                                            StatusID = VCStatus.StatusID,
                                            StatusOrder = VCStatus.StatusOrder,
                                            StatusDesc = VCStatus.StatusDesc,
                                            Active = VCStatus.Active,
                                        }).Take(Data.PageNum * SetupVConfStatusGridBData.PageCount).ToList();
            SearchedVConfStatus.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return SearchedVConfStatus;
        }
        public ErrorBInfo SaveVConfStatusDetailsData(SetupVConfStatusGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> StatusIDs = Data.Rows.Select(VCS => VCS.StatusID).ToList();
                var SaveEntityVConfStatus = _context.tblVConfirmStatus.Where(VCS => StatusIDs.Contains(VCS.StatusID)).ToList();
                foreach (var VCStatus in SaveEntityVConfStatus)
                {
                    var SaveData = Data.Rows.Where(data => data.StatusID == VCStatus.StatusID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        VCStatus.StatusOrder = SaveData.StatusOrder;
                        VCStatus.StatusDesc = SaveData.StatusDesc;
                        VCStatus.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        #region SpecialDatesRepository
        public ErrorBInfo CreateSpecialDate(SetupSpecialDatesGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                tblSpecialDate NewSpecialDate = new tblSpecialDate()
                {
                    VendorID = Data.Rows[0].VendorID,
                    StartDate = Data.Rows[0].StartDate,
                    EndDate = Data.Rows[0].EndDate,
                    Notes = Data.Rows[0].Notes,
                    CountryID = Data.Rows[0].CountryID,
                    CityID = Data.Rows[0].SpecialDatesCityID,
                    IsHotel = Data.Rows[0].IsHotel,
                    IsAttraction = Data.Rows[0].IsAttraction,
                    IsRestaurant = Data.Rows[0].IsRestaurant,
                    IsMisc = Data.Rows[0].IsMisc,
                    IsBus = Data.Rows[0].IsBus,
                    Active = Data.Rows[0].Active,
                };
                _context.tblSpecialDates.Add(NewSpecialDate);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 100;
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public SetupSpecialDatesGridBData LoadSpecialDates(int PageNumber)
        {
            SetupSpecialDatesGridBData SpecialDates = new SetupSpecialDatesGridBData();
            if (PageNumber == -1)
            {
                SpecialDates.CountryID = (from country in _context.tblCountries
                                          where country.Active == true
                                          orderby country.CountryID
                                          select new
                                          {
                                              Key = country.CountryID,
                                              Value = country.CountryName,
                                          }).ToDictionary(o => o.Key, o => o.Value);
                SpecialDates.Rows = new List<SetupSpecialDatesGridRowBData>();
                if (SpecialDates.CountryID != null)
                {
                    SpecialDates.Rows.Add(new SetupSpecialDatesGridRowBData
                    {
                        SpecialDatesCityID = null,
                    });
                    int CityFirstKey = SpecialDates.CountryID.First().Key;
                    foreach (var Data in SpecialDates.Rows)
                    {
                        Data.CityID = (from city in _context.tblCities
                                       where CityFirstKey == city.CountryID
                                       && city.Active == true
                                       orderby city.City
                                       select new
                                       {
                                           Key = city.CityID,
                                           Value = city.City,
                                       }).ToDictionary(o => o.Key, o => o.Value);
                    }

                }
                return SpecialDates;
            }
            SpecialDates.Rows = new List<SetupSpecialDatesGridRowBData>();
            SpecialDates.Rows = (from spdates in _context.tblSpecialDates
                                 orderby spdates.StartDate
                                 select new SetupSpecialDatesGridRowBData()
                                 {
                                     SpecialDatesID = spdates.SpecialDatesID,
                                     VendorID = spdates.VendorID,
                                     VendorName =_context.tblVendors.Any(vnd => spdates.VendorID == vnd.VendorID) ? _context.tblVendors.Where(vnd => spdates.VendorID == vnd.VendorID).FirstOrDefault().VendorName : "",
                                     StartDate = spdates.StartDate,
                                     EndDate = spdates.EndDate,
                                     CountryID = spdates.CountryID,
                                     SpecialDatesCityID = spdates.CityID,
                                     Notes = spdates.Notes,
                                     IsHotel = spdates.IsHotel,
                                     IsAttraction = spdates.IsAttraction,
                                     IsRestaurant = spdates.IsRestaurant,
                                     IsMisc = spdates.IsMisc,
                                     IsBus = spdates.IsBus,
                                     Active = spdates.Active,
                                 }).Take(PageNumber * SetupDetailsGridBData.PageCount).ToList();
            foreach (var Data in SpecialDates.Rows)
            {
                Data.CityID = (from City in _context.tblCities
                               where City.CountryID == Data.CountryID
                               && City.Active == true
                               orderby City.City
                               select new
                               {
                                   Key = City.CityID,
                                   Value = City.City,
                               }).ToDictionary(o => o.Key, o => o.Value);
            }
            SpecialDates.CountryID = (from country in _context.tblCountries
                                      where country.Active == true
                                      orderby country.CountryID
                                      select new
                                      {
                                          Key = country.CountryID,
                                          Value = country.CountryName,
                                      }).ToDictionary(o => o.Key, o => o.Value);
            SpecialDates.CityID = (from City in _context.tblCities
                                   where City.Active == true
                                   && City.CountryID == 1
                                   orderby City.City
                                   select new
                                   {
                                       Key = City.CityID,
                                       Value = City.City,
                                   }).ToDictionary(o => o.Key, o => o.Value);
            SpecialDates.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return SpecialDates;
        }
        public SetupSpecialDatesGridBData LoadSearchedSpecialDates(SetupSpecialDatesGridBData Data)
        {
            var Filter = _context.tblSpecialDates.AsQueryable();
            if (Data.SpecialDatesCityID != null && Data.SpecialDatesCityID != 0)
                Filter = Filter.Where(SpecialDates => SpecialDates.CityID == Data.SpecialDatesCityID);
            if (Data.VendorID != null && Data.VendorID != 0)
                Filter = Filter.Where(SpecialDates => SpecialDates.VendorID == Data.VendorID);
            if (Data.StartDate != null)
                Filter = Filter.Where(SpecialDates => SpecialDates.StartDate >= Data.StartDate);
            if (Data.EndDate != null)
                Filter = Filter.Where(SpecialDates => SpecialDates.EndDate <= Data.EndDate);
            if (Data.SelectedActive != null)
                Filter = Filter.Where(Detail => Detail.Active == Data.SelectedActive);
            SetupSpecialDatesGridBData Searched;
            Searched = new SetupSpecialDatesGridBData();
            Searched.Rows = new List<SetupSpecialDatesGridRowBData>();
            Searched.Rows = (from spdates in Filter
                             orderby spdates.StartDate
                             select new SetupSpecialDatesGridRowBData()
                             {
                                 SpecialDatesID = spdates.SpecialDatesID,
                                 VendorID = spdates.VendorID,
                                 VendorName = _context.tblVendors.Any(vnd => spdates.VendorID == vnd.VendorID) ? _context.tblVendors.Where(vnd => spdates.VendorID == vnd.VendorID).FirstOrDefault().VendorName : "",
                                 StartDate = spdates.StartDate,
                                 EndDate = spdates.EndDate,
                                 CountryID = spdates.CountryID,
                                 SpecialDatesCityID = spdates.CityID,
                                 Notes = spdates.Notes,
                                 IsHotel = spdates.IsHotel,
                                 IsAttraction = spdates.IsAttraction,
                                 IsRestaurant = spdates.IsRestaurant,
                                 IsMisc = spdates.IsMisc,
                                 IsBus = spdates.IsBus,
                                 Active = spdates.Active,
                             }).Take(Data.PageNum * SetupDetailsGridBData.PageCount).ToList();
            foreach (var row in Searched.Rows)
            {
                row.CityID = (from City in _context.tblCities
                              where City.CountryID == row.CountryID
                              && City.Active == true
                              orderby City.City
                              select new
                              {
                                  Key = City.CityID,
                                  Value = City.City,
                              }).ToDictionary(o => o.Key, o => o.Value);
            }
            Searched.CityID = (from City in _context.tblCities
                               where City.Active == true
                               && City.CountryID == 1
                               orderby City.City
                               select new
                               {
                                   Key = City.CityID,
                                   Value = City.City,
                               }).ToDictionary(o => o.Key, o => o.Value);
            Searched.CountryID = (from country in _context.tblCountries
                                  where country.Active == true
                                  orderby country.CountryID
                                  select new
                                  {
                                      Key = country.CountryID,
                                      Value = country.CountryName,
                                  }).ToDictionary(o => o.Key, o => o.Value);
            Searched.Active = new Dictionary<bool, string>
            {
                {true,"Yes"},
                {false,"No"},
            };
            return Searched;
        }
        public ErrorBInfo DeleteSpecialDateById(int SpecialDateID)
        {
            ErrorBInfo Error = new ErrorBInfo();
            var SpecialDate = _context.tblSpecialDates.Where(SpDate => SpDate.SpecialDatesID == SpecialDateID).SingleOrDefault();
            try
            {
                _context.tblSpecialDates.Remove(SpecialDate);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.errorCode = 103;
                Error.Message = "This Special Date already use from another table";
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        public Dictionary<int, string> GetCity(int? CountryID)
        {
            Dictionary<int, string> Cities = new Dictionary<int, string>();
            if (CountryID != null && CountryID != 0)
            {
                Cities = (from City in _context.tblCities
                          where City.CountryID == CountryID
                          && City.Active == true
                          orderby City.City
                          select new
                          {
                              Key = City.CityID,
                              Value = City.City,
                          }).ToDictionary(o => o.Key, o => o.Value);
            }
            else
            {
                Cities = (from City in _context.tblCities
                          where City.Active == true
                          orderby City.City
                          select new
                          {
                              Key = City.CityID,
                              Value = City.City,
                          }).ToDictionary(o => o.Key, o => o.Value);
            }
            return Cities;
        }
        public ErrorBInfo SaveSpecialDatesDetailsData(SetupSpecialDatesGridBData Data)
        {
            ErrorBInfo Error = new ErrorBInfo();
            try
            {
                List<int> SpecialDatesIDs = Data.Rows.Select(spdates => spdates.SpecialDatesID).ToList();
                var SaveEntitySpecialDates = _context.tblSpecialDates.Where(spdates => SpecialDatesIDs.Contains(spdates.SpecialDatesID)).ToList();
                foreach (var spdates in SaveEntitySpecialDates)
                {
                    var SaveData = Data.Rows.Where(data => data.SpecialDatesID == spdates.SpecialDatesID).SingleOrDefault();
                    if (SaveData != null)
                    {
                        spdates.VendorID = SaveData.VendorID;
                        spdates.CountryID = SaveData.CountryID;
                        spdates.CityID = SaveData.SpecialDatesCityID;
                        spdates.Notes = SaveData.Notes;
                        spdates.StartDate = SaveData.StartDate;
                        spdates.EndDate = SaveData.EndDate;
                        spdates.IsHotel = SaveData.IsHotel;
                        spdates.IsAttraction = SaveData.IsAttraction;
                        spdates.IsRestaurant = SaveData.IsRestaurant;
                        spdates.IsMisc = SaveData.IsMisc;
                        spdates.IsBus = SaveData.IsBus;
                        spdates.Active = SaveData.Active;
                    }
                }
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                Error.Message = ex.Message;
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return Error;
        }
        #endregion
        public Dictionary<string, string> LoadState()
        {
            Dictionary<string, string> States = new Dictionary<string, string>();
            States = (from state in _context.tblStates where state.Active == true select new { Key = state.StateID, Value = state.StateID }).ToDictionary(o => o.Key, o => o.Value);
            if (States == null)
            {
                States = new Dictionary<string, string>();
            }
            return States;
        }
        public Dictionary<int, string> LoadCity(int CountryID, string StateID)
        {
            Dictionary<int, string> City = new Dictionary<int, string>();
            if (CountryID != 0)
            {
                City = (from city in _context.tblCities where city.CountryID == CountryID &&  city.Active == true orderby city.City select new { Key = city.CityID, Value = city.City }).ToDictionary(o => o.Key, o => o.Value);
            }
            else
            {
                City = (from city in _context.tblCities where city.StateID == StateID && city.Active == true orderby city.City select new { Key = city.CityID, Value = city.City }).ToDictionary(o => o.Key, o => o.Value);
            }
            if (City == null)
                City = new Dictionary<int, string>();
            return City;
        }
        public void Dispose()
        {
            _context.Dispose();
        }
    }
}
